Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
List Database Users and Roles
SELECT DISTINCT
dbp.name AS DBUser,
dbp.type_desc AS UserType,
dbp.create_date AS CreateDate,
SUBSTRING(
(
SELECT DISTINCT ';' + roles2.name AS [text()]
FROM sys.database_principals AS dbp2
LEFT JOIN sys.database_role_members AS dbrm2
LEFT JOIN sys.database_principals AS roles2
ON dbrm2.role_principal_id = roles2.principal_id
ON dbp.principal_id = dbrm2.member_principal_id
WHERE dbp2.type IN ( 'S', 'U', 'G' )
AND dbp2.principal_id > 4
FOR XML PATH('')
),
2,
1000
) [Roles]
FROM sys.database_principals AS dbp
WHERE dbp.type IN ( 'S', 'U', 'G' )
AND dbp.principal_id > 4;
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.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.