Skip to content

Instantly share code, notes, and snippets.

@naranjja

naranjja/users-permissions.sql

Created August 20, 2019 00:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save naranjja/b5f5ef0623f59f1f195bdb3d77f219cf to your computer and use it in GitHub Desktop.
Save naranjja/b5f5ef0623f59f1f195bdb3d77f219cf to your computer and use it in GitHub Desktop.
Download ZIP
List all users and permissions in Azure SQL
Raw
users-permissions.sql
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment