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?

Overarm

Not really a full answer but check out this article for details about those roles: msdn.microsoft.com/en-us/library/azure/ee336235.aspx – Stoll

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.

Halette

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;  

Jamey

Gold. Saved me. THank you – Injurious

Answer 3

Building on @tmullaney 's answer, you can also left join in the sys.objects view to get insight when explicit permissions have been granted on objects. Make sure to use the LEFT join:

SELECT DISTINCT pr.principal_id, pr.name AS [UserName], pr.type_desc AS [User_or_Role], pr.authentication_type_desc AS [Auth_Type], pe.state_desc,
    pe.permission_name, pe.class_desc, o.[name] AS 'Object' 
    FROM sys.database_principals AS pr 
    JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
    LEFT JOIN sys.objects AS o on (o.object_id = pe.major_id)

Keane

This does not work as intended when the object to which permission is granted is a SCHEMA. You must also LEFT JOIN to sys.schemas when class_desc = "SCHEMA" – Tracitracie

Answer 4

Further building on @brentlightsey 's answer, you can add left join to sys.schemas to also see permissions on a schema level:

SELECT DISTINCT 
       pr.principal_id
     , pr.name AS [UserName]
     , pr.type_desc AS [User_or_Role]
     , pr.authentication_type_desc AS [Auth_Type]
     , pe.state_desc
     , pe.permission_name
     , pe.class_desc
     , coalesce(o.[name], sch.name) AS [Object]
FROM sys.database_principals AS pr
    JOIN sys.database_permissions AS pe
        ON pe.grantee_principal_id = pr.principal_id
    LEFT JOIN sys.objects AS o
        ON o.object_id = pe.major_id
    LEFT JOIN sys.schemas AS sch
        ON sch.schema_id = pe.major_id
        AND class_desc = 'SCHEMA'

Catalase

Answer 5

if you want to find about object name e.g. table name and stored procedure on which particular user has permission, use the following query:

SELECT pr.principal_id, pr.name, pr.type_desc, 
    pr.authentication_type_desc, pe.state_desc, pe.permission_name, OBJECT_NAME(major_id) objectName
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
--INNER JOIN sys.schemas AS s ON s.principal_id =  sys.database_role_members.role_principal_id 
     where pr.name in ('youruser1','youruser2') 

Singhal

Based on the post you've made, I would recommend spending some time reading documentation in the Help Center so that you have the best chance at getting your question answered. – Strother