Simply update the use statement with your database name and run the query, it will show you all your denies on database objects and to what role and user they are denied
2016-05-23
1,415 reads
Simply update the use statement with your database name and run the query, it will show you all your denies on database objects and to what role and user they are denied
USE <yourdatabasenamehere> SELECT l.name as grantee_name, p.state_desc, p.permission_name, o.name FROM sys.database_permissions AS p JOIN sys.database_principals AS l ON p.grantee_principal_id = l.principal_id JOIN sys.sysobjects O ON p.major_id = O.id WHERE p.state_desc ='DENY'