User cannot select from table but their permission appear to show that they should be able to

Question

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.

Answer 1

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.

Answer 2

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.