Menu
SQL Server Administration: List all principal and object permissions with the following query. Use this to periodically check that permissions have been properly set for access to objects in your databases.
SELECT USER_NAME(p.grantee_principal_id) AS principal_name,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp
ON p.grantee_principal_id = dp.principal_id