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 with this?

Answer 1

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;  

You can master these queries and become proficient in SQL queries by enrolling in an industry-recognized SQL certification.