In SQL Azure if you connect up Microsoft SQL Server Management Studio, you have to do everything using SQL statements, there is no ability to point and click your way through creating accounts, memberships, new tables etc. I’m sure a good DBA would tell me that this is the correct way of building any database. Unfortunately, (or fortunately) I’m not a DBA, and I like point and click tools.
So the other day I was having a problem seeing what accounts had what access to a given database. I found running this SQL statement on a given database gave me the information I needed. I have written this blog post today mainly so I have reference to this in the future.
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;
Fig 1. Example results.