Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- Returns server level privileges.
-- Reference: http://msdn.microsoft.com/en-us/library/ms186260.aspx
SELECT GRE.name AS Grantee
,GRO.name AS Grantor
,PER.class_desc AS PermClass
,PER.permission_name AS PermName
,PER.state_desc AS PermState
,COALESCE(PRC.name, EP.name, N'') AS ObjectName
,COALESCE(PRC.type_desc, EP.type_desc, N'') AS ObjectType
FROM [sys].[server_permissions] AS PER
INNER JOIN sys.server_principals AS GRO
ON PER.grantor_principal_id = GRO.principal_id
INNER JOIN sys.server_principals AS GRE
ON PER.grantee_principal_id = GRE.principal_id
LEFT JOIN sys.server_principals AS PRC
ON PER.class = 101
AND PER.major_id = PRC.principal_id
LEFT JOIN sys.endpoints AS EP
ON PER.class = 105
AND PER.major_id = EP.endpoint_id
ORDER BY Grantee,PermName;
-- This will return all of the database user privileges for the current database.
-- Reference: http://msdn.microsoft.com/en-us/library/ms188367.aspx
SELECT distinct class_desc as [CLASS_DESC],
c.name AS [GRANTOR],
b.name AS [GRANTEE],
b.type_desc AS [PRINCIPAL_TYPE],
ISNULL(SCH.name + N'.' + OBJ.name,DB_NAME()) AS [OBJECT_NAME],
a.permission_name AS [PERMISSION_NAME],
a.state_desc AS [PERMISSION_STATE]
FROM [sys].[database_permissions] a
INNER JOIN [sys].[database_principals] b
ON a.grantee_principal_id = b.principal_id
INNER JOIN [sys].[database_principals] c
ON a.grantor_principal_id = c.principal_id
LEFT JOIN [sys].[objects] AS OBJ
ON a.major_id = OBJ.object_id
LEFT JOIN [sys].[schemas] AS SCH
ON OBJ.schema_id = SCH.schema_id
LEFT JOIN [sys].[columns] AS COL
ON a.major_id = COL.object_id
AND a.minor_id = COL.column_id
ORDER BY CLASS_DESC,GRANTEE
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.