Feeds:
Posts
Comments

Posts Tagged ‘Roles’

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;">&#65279;</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 &gt;= 0      --     Ignore system objects
ORDER BY P.class_desc, L.name ASC
GO

DetailedPermissions

Hope this helps,
_Sqltimes

Read Full Post »