CREATE VIEW Metadata.RoleMembersRecursive
AS
WITH 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],
[UserType],
[Level],
HierarchyTree,
IsFixedRole,
CreateDate,
[MemberId],
[RoleId]
FROM MemberLinks