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.

Answer 2

To see who has what roles and what permissions assigned to any given user in Azure SQL Database or SQL Server, do this:

To retrieve roles and role memberships for a user: your query is great for listing the database roles a user is a member of. It's returning loginmanager and dbmanager. Follow the code below:

SELECT

r.name AS role_principal_name, 

    m.name AS member_principal_name 

 

FROM 

sys.database_role_members rm 

 

INNER JOIN sys.database_principals r ON im.role_principal_id = r.principal_id

INNER JOIN sys.database_principals m ON im.member_principal_id = m.principal_id

 

WHERE m.name = 'your_user_name';.

 

To get permissions to certain roles such as loginmanager and dbmanager you can make a query like this:

SELECT 

    dp.class_desc, 

    dp.permission_name, 

    dp.state_desc, 

    pr.name AS principal_name

FROM 

    sys.database_permissions dp

JOIN

sys.database_principals pr ON dp.grantee_principal_id = pr.principal_id WHERE pr.name IN ('loginmanager', 'dbmanager')

 

This query will show all the permissions that are permission_name and the state for each of these permissions in terms of either GRANT, DENY, or REVOKE pertaining to a role.

 

These queries will make you understand which roles and permissions are applied to any user or role within Azure SQL Database or SQL Server.