db_owner Member Can't Delete on Tables

Question

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.

Answer 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.

Answer 2

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

Answer 3

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.