CREATE VIEW Metadata.PERMISSIONS
AS
WITH Perm
AS (SELECT d.CLASS,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
p.name COLLATE SQL_Latin1_General_CP1_CI_AS AS UserName,
s.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectName,
Permission,
StateDesc AS StateDesc,
StateDesc + ' ' + Permission + ' ON SCHEMA::[' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + '] TO [' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ '];' AS GrantSQL,
'REVOKE ' + Permission + ' ON SCHEMA::[' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + '] TO [' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ '];' AS RevokeSQL,
p.principal_id
FROM sys.database_permissions d (NOLOCK)
INNER JOIN sys.database_principals p (NOLOCK) ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = d.major_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission) ca
WHERE d.CLASS = 3 /*'SCHEMA'*/
AND p.type COLLATE SQL_Latin1_General_CP1_CI_AS IN ('U', 'G', 'S', 'R')
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'cdc')
UNION ALL
SELECT d.CLASS,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
p.name COLLATE SQL_Latin1_General_CP1_CI_AS AS UserName,
DB_NAME() AS ObjectName,
Permission,
StateDesc AS StateDesc,
StateDesc + ' ' + Permission + ' TO [' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS + '];' AS GrantSQL,
'REVOKE ' + Permission + ' TO [' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS + '];' AS RevokeSQL,
p.principal_id
FROM sys.database_permissions d (NOLOCK)
INNER JOIN sys.database_principals p (NOLOCK) ON d.grantee_principal_id = p.principal_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission) ca
WHERE d.CLASS = 0 /*DATABASE*/
AND p.type COLLATE SQL_Latin1_General_CP1_CI_AS IN ('U', 'G', 'S', 'R')
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'cdc')
UNION ALL
SELECT d.CLASS,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
p.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS UserName,
'[' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + '].[' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' + ISNULL(' ([' + co.NAME COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']) ', '') AS ObjectName,
Permission,
StateDesc AS StateDesc,
StateDesc + ' ' + Permission + ' ON [' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + '].[' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ '] ' + ISNULL('([' + co.NAME COLLATE SQL_Latin1_General_CP1_CI_AS + ']) ', '') + 'TO [' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ '];' AS GrantSQL,
'REVOKE ' + Permission + ' ON [' + s.name COLLATE SQL_Latin1_General_CP1_CI_AS + '].[' + o.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ '] ' + ISNULL('([' + co.NAME COLLATE SQL_Latin1_General_CP1_CI_AS + ']) ', '') + 'TO [' + p.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ '];' AS RevokeSQL,
p.principal_id
FROM sys.database_permissions d (NOLOCK)
INNER JOIN sys.database_principals p (NOLOCK) ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.objects o (NOLOCK) ON o.OBJECT_ID = d.major_id
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
LEFT OUTER JOIN sys.columns co ON co.OBJECT_ID = o.OBJECT_ID
AND co.column_id = d.minor_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS Permission) ca
WHERE d.CLASS = 1 /*OBJECT OR COLUMN*/
AND p.type COLLATE SQL_Latin1_General_CP1_CI_AS IN ('U', 'G', 'S', 'R')
AND p.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'cdc'))
SELECT CLASS,
ClassName,
UserName,
ObjectName,
Permission,
StateDesc,
GrantSQL,
RevokeSQL,
principal_id AS PrincipalId
FROM Perm