How to view the roles and permissions granted to any database user in Azure SQL server instance?

Question

Could you guide me on how to view the current roles/permissions granted to any database user in Azure SQL Database or in general for a MSSQL Server instance?

I have this below query:

SELECT r.name role_principal_name, m.name AS member_principal_name FROM sys.database_role_members rm  JOIN sys.database_principals r      ON rm.role_principal_id = r.principal_id JOIN sys.database_principals m      ON rm.member_principal_id = m.principal_id WHERE r.name IN ('loginmanager', 'dbmanager'); 

I further need to know what are the permissions granted to these roles "loginmanager" and "dbmanager"?

Could you help me on this?

Answer 1

Per the MSDN documentation for sys.database_permissions, this query lists all permissions explicitly granted or denied to principals in the database you're connected to:

SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc,      pr.authentication_type_desc, pe.state_desc, pe.permission_name FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe     ON pe.grantee_principal_id = pr.principal_id; 

Per Managing Databases and Logins in Azure SQL Database, the loginmanager and dbmanager roles are the two server-level security roles available in Azure SQL Database. The loginmanager role has permission to create logins, and the dbmanager role has permission to create databases. You can view which users belong to these roles by using the query you have above against the master database. You can also determine the role memberships of users on each of your user databases by using the same query (minus the filter predicate) while connected to them.

Answer 2

To view database roles assigned to users, you can use sys.database_role_members

The following query returns the members of the database roles.

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;