1

I attempted to revoke delete permissions to most users in a database by using the deny and revoke sql commands and checking the deny box for delete when adding the table as a securable in the UI.

When I did that, the users are now not able to select from any table in the database. They have select permission granted in the UI for all tables including the table I am trying to revoke delete permissions to.

I tried to recreate what I did on a backup copy of the database and I have not been able to deny select on the tables.

The only deny permissions I found on any of the users were deny delete on all objects and deny on the one table I am trying to prevent delete on.

I did not find any deny selects on any users or roles.

No users are part of the db_denydatareader role.

What is the best way to find out why the users are not able to select from any table in the original database?

I am using SQL Server 2008.

2

I would check if the users are part of the db_denydatareader role, check for deny permissions on the tables schema, and also for a deny permission on the public role. I would suspect these are the most likely culprits.

  • The only deny permissions I found on any of the users were deny delete on all objects and deny on the one table I am trying to prevent delete on. I did not find any deny selects on any users or roles. No users are part of the db_denydatareader role. – Courtney Saganski May 13 '16 at 11:44
1

Try running this, when in the Database - it will give you the permission levels for SERVER, DATABASE, OBJECTs in 3 separate result windows - you can then consider the Group membership of the user vs the permissions levels (copy/paste results into Excel for easier filtering, etc. (PS. not my code, but very useful - I would reference the original posting/poster, but can't find/remember!)):

SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
          WHEN b.securityadmin=1 THEN 'securityadmin'
          WHEN b.serveradmin=1 THEN 'serveradmin'
          WHEN b.setupadmin=1 THEN 'setupadmin'
          WHEN b.processadmin=1 THEN 'processadmin'
          WHEN b.diskadmin=1 THEN 'diskadmin'
          WHEN b.dbcreator=1 THEN 'dbcreator'
          WHEN b.bulkadmin=1 THEN 'bulkadmin'
          ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a  JOIN master..syslogins b ON a.sid=b.sid WHERE a.type  <> 'R' AND a.name NOT LIKE '##%'


DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM [?].sys.database_principals a
LEFT OUTER JOIN [?].sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from [?].sys.database_permissions
join [?].sys.sysusers U on grantee_principal_id = uid join [?].sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table

Hope this helps, let us know how you get on.

  • I am still only seeing that they have deny delete on all objects for some users and deny delete on that one table for the other users. The user I am most concerned with is on the db_owner role, with deny delete on one table. – Courtney Saganski May 13 '16 at 12:58
  • Caution - sp_MSforeachdb is undocumented, not reliable and can skip databases. If you still want to use - use a reliable sp_MSforeachdb ! – Kin Shah May 13 '16 at 13:24
  • Hmm, a difficult one without being able to see it. What EXACT error message do the users get? -are they connecting using a front end app of some sort? -if you get them to log on via SSMS do they get the same error? -if you create a test user with the same permissions as the users, do you get the same behaviour? Are the users a member of a group/role that you aren't considering? (they can be easy to overlook). Create a Group with the exact permissions you need, add a test user, make sure that user works as expected, add the 'users' to that group and remove their membership from everything else. – MHSQLDBA May 16 '16 at 9:38
  • Obviously - be careful when doing any of the above if your permission structure is difficult to rebuild and especially as you are somewhat unsure of the effect of your changes. (Test in a dev environment first where poss) – MHSQLDBA May 16 '16 at 9:41
  • Check ownership of the table too - for something like this (ownership chaining): sqlmag.com/sql-server/complicated-permissions – MHSQLDBA May 16 '16 at 9:49

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.