May 2010 Tip of the Month

By SQLRx Admin | Helpful Scripts

May 28

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,


OBJECT_NAME(p.major_id) AS object_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

About the Author