CREATE VIEW Metadata.PermissionsRecursive
AS
WITH Perm
AS (SELECT d.CLASS,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
s.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectName,
Permission,
StateDesc AS StateDesc,
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'*/
UNION ALL
SELECT d.CLASS,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
DB_NAME() AS ObjectName,
Permission,
StateDesc AS StateDesc,
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*/
UNION ALL
SELECT d.CLASS,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS ClassName,
'[' + 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,
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')),
RoleMembers
AS (SELECT rp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS RoleName,
mp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
a.role_principal_id AS RoleId,
a.member_principal_id AS MemberId
FROM sys.database_role_members a (NOLOCK)
INNER JOIN sys.database_principals rp (NOLOCK) ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals mp (NOLOCK) ON mp.principal_id = a.member_principal_id
WHERE mp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'cdc')
AND mp.type COLLATE SQL_Latin1_General_CP1_CI_AS IN ('U', 'G', 'S', 'R')),
users
AS (SELECT Name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
principal_id AS MemberId,
type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS UserType,
create_date AS CreateDate,
is_fixed_role AS IsFixedRole
FROM sys.database_principals (NOLOCK)
WHERE TYPE IN ('S', 'U', 'G', 'R')
AND NAME NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'cdc')),
MemberLinks
AS (SELECT MemberName AS RoleName,
MemberId AS RoleId,
MemberName,
MemberId,
UserType,
CreateDate,
IsFixedRole,
0 AS [Level],
CAST('' AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CI_AS AS HierarchyTree
FROM users
UNION ALL
SELECT r.RoleName,
r.RoleId,
l.MemberName,
l.MemberId,
l.UserType,
NULL AS CreateDate,
NULL AS IsFixedRole,
l.[Level] + 1 AS [Level],
CAST(l.HierarchyTree COLLATE SQL_Latin1_General_CP1_CI_AS + CASE WHEN l.HierarchyTree COLLATE SQL_Latin1_General_CP1_CI_AS = '' THEN ''
ELSE ','
END + r.RoleName COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CI_AS AS HierarchyTree
FROM MemberLinks l
INNER JOIN RoleMembers r ON r.MemberId = l.RoleId)
SELECT MemberName,
RoleName,
[Level],
UserType,
0 AS CLASS,
'ROLE_MEMBER' AS ClassName,
RoleName AS ObjectName,
'ROLE_MEMBER' AS Permission,
'GRANT' AS StateDesc,
HierarchyTree,
RoleId,
MemberId
FROM MemberLinks
WHERE RoleId <> MemberId
UNION ALL
SELECT a.MemberName,
a.RoleName,
[Level],
a.UserType,
p.CLASS,
p.ClassName,
p.ObjectName,
p.Permission,
p.StateDesc,
HierarchyTree,
a.RoleId,
a.MemberId
FROM MemberLinks a
INNER JOIN Perm p ON p.principal_id = a.RoleId