May 2010 Tip of the Month

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

Both comments and trackbacks are currently closed.
%d bloggers like this: