CREATE VIEW Metadata.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,
mp.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberType,
mp.Type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberTypeName,
'EXEC sp_addrolemember N''' + rp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''', N''' + mp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''' AS AddSQL,
'EXEC sp_droprolemember N''' + rp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ''', N''' + mp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '''' AS DropSQL,
mp.principal_id AS MemberPrincipalId,
rp.principal_id AS RolePrincipalId
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')