3

SQL server 2008 R2.

A user is part of an AD group. That AD group has a SQL account in this instance. That group has the following access to a Database:

  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_executor
  • db_owner
  • MSReplPal_7_1
  • public

When that user runs "Delete" on this database, it throws an error:

The DELETE permission was denied on the object '', database '', schema 'dbo'.

User belongs to some other groups but none of those groups has permission to this DB in question.

This group does not have explicitly deny on the table in question.

What could the reason the user cannot delete on tables?

Also, I don't understand how this group has given this many access on this database. Doesn't having "db_owner" imply the group has maximum access to the database and other permission don't need to be added?? such as db_datareader, db_datawriter, etc.

Thanks for your time.

8
  • 2
    Maybe one of the other groups has explicit DENY. DENY always overrides GRANT. Jun 23, 2014 at 17:07
  • None of the groups he is part of have explicit deny on that database. Jun 23, 2014 at 17:57
  • 3
    Well SQL Server doesn't make up fake permission denied errors, so there is some reason they can't delete from this table. Did you carefully inspect sys.database_permissions for every group/role they might be part of? Jun 23, 2014 at 18:18
  • 1
    Execute [EXEC xp_logininfo 'Domain\UserName','all'] to see what groups SQL Server picks up for the user. Then verify what permissions each one of those groups returned within "permission path" has for the database.
    – user507
    Jun 23, 2014 at 19:30
  • 1
    You can't revoke rights to a member of db_owner. Is there a trigger on the table that's trying to delete data from a table in another database?
    – mrdenny
    Jun 25, 2014 at 21:49

3 Answers 3

1

Unless the users is mapped to the sysadmin fixed server role (where they will connect to the database as the user dbo) then they can be limited by security on the database. (i.e. A member of db_owner will receive a security check where dbo will not.)

A quick test to see if it is a permissions issue is to temporarily grant a user sysadmin role and then see if they can delete data.... if not then I'd start looking for constraints on tables.

If they can then run the following against the database (you may get more you may need to add DELETE to your where clause):

select * from sys.database_permissions 
where state_desc = 'DENY'

If permissions are set it will give you something like:

class class_desc                                                   major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name                                                                                                                  state state_desc
----- ------------------------------------------------------------ ----------- ----------- -------------------- -------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------
0     DATABASE                                                     0           0           10                   1                    DL   DELETE                                                                                                                           D     DENY
1     OBJECT_OR_COLUMN                                             1501607433  0           10                   1                    DL   DELETE                                                                                                                           D     DENY

(2 row(s) affected)

If deny delete is set at the DATABASE level then well then there is your problem. Adjust permissions so required users are not actually DENYed access.

0

If the table is replicated. (SELECT * FROM sys.tables where is_replicated = 1). Then only SA role is able to execute "DELETE"

1
  • I guess the table is enabled "CDC" feature. If it's, then the CDC script does "DENY" on DELETE INSERT and UPDATE by default (You can script out the deployment script).
    – user92381
    Apr 18, 2016 at 15:08
-1

check groups this user is part of. In this scenario user should be part of some group which has been specifically denied access for dml operations. i have seen this many times.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.