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.

  • 2
    Maybe one of the other groups has explicit DENY. DENY always overrides GRANT. – Aaron Bertrand Jun 23 '14 at 17:07
  • None of the groups he is part of have explicit deny on that database. – user3618129 Jun 23 '14 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? – Aaron Bertrand Jun 23 '14 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. – Shawn Melton Jun 23 '14 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 '14 at 21:49
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"

  • 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 '16 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, privacy policy and cookie policy

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