Interesting one today:
Quite often, we need a quick way to check if correct permissions are granted on the database objects. Since there could be thousands of such objects with hundreds of different possible permissions, this gets a bit daunting.
In the past, we’ve seen some dynamic between Roles, Logins, Users & Permissions. Today, we’ll look into a quick way to a query to retrieve permissions on all objects for all principals; Then we can filter further and focus on the objects/logins we need.
-- -- Detailed Permissions<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span> -- SELECT P.class_desc AS [Permission_Category] , P.grantee_principal_id AS [Principal_ID] , L.name AS [Principal_Name] , L.type_desc AS [Principal_Type] , P.major_id AS [ID_of_Resource] , CASE P.class_desc WHEN 'CERTIFICATE' THEN C.name WHEN 'SYMMETRIC_KEYS' THEN S.name WHEN 'TYPE' THEN T.name ELSE O.name END AS [ObjectName] , O.type_desc AS [Detailed_Object_Type] , P.state_desc AS [Permission] , G.name AS [Who_Gave_This_Permission] FROM sys.database_permissions AS P INNER JOIN sys.database_principals AS L ON L.principal_id = P.grantee_principal_id LEFT OUTER JOIN sys.database_principals AS G ON G.principal_id = P.grantor_principal_id LEFT OUTER JOIN sys.objects AS O ON O.object_id = P.major_id LEFT OUTER JOIN sys.symmetric_keys AS S ON S.symmetric_key_id = P.major_id LEFT OUTER JOIN sys.Certificates AS C ON C.certificate_id = P.major_id LEFT OUTER JOIN sys.types AS T ON T.user_type_id = P.major_id WHERE P.major_id >= 0 -- Ignore system objects ORDER BY P.class_desc, L.name ASC GO
_Sqltimes
Leave a comment