如何在Azure SQL服务器实例中查看授予任何数据库用户的角色和权限？

Question

你能指导我如何查看Azure SQL数据库或MSSQL Server实例中任何数据库用户被授予的当前角色/权限吗？

我有以下查询：

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');

我还需要知道这些角色“loginmanager”和“dbmanager”被授予了哪些权限？

你能帮我吗？

- user3258784

---

这并不是一个完整的答案，但是请查看此文章以了解有关这些角色的详细信息：https://msdn.microsoft.com/zh-cn/library/azure/ee336235.aspx - cbattlegear

Answer 1

根据MSDN文档中关于sys.database_permissions的说明，此查询列出了你所连接到的数据库中授予或拒绝给主体的所有显式权限：

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;

根据Azure SQL 数据库中的数据库和登录管理文章，loginmanager 和 dbmanager 角色是 Azure SQL 数据库中可用的两个服务器级别安全角色。 loginmanager 角色具有创建登录的权限，而 dbmanager 角色具有创建数据库的权限。您可以使用上面的查询针对 master 数据库查看哪些用户属于这些角色。在连接到各自的用户数据库时，使用相同的查询（去掉筛选谓词）还可以确定用户在每个用户数据库的角色成员身份。

- tmullaney

Answer 2

要查看分配给用户的数据库角色，您可以使用 sys.database_role_members

以下查询返回数据库角色的成员。

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;  

- Thomas

---

黄金。救了我。谢谢。 - romanzdk

这正是我正在寻找的。谢谢。 - undefined

Answer 3

在@tmullaney的回答基础上，您还可以在sys.objects视图中使用左连接来获得授予对象显式权限的见解。确保使用LEFT JOIN：

建立在@tmullaney的回答之上，您也可以在sys.objects视图中进行左联接，以便了解何时向对象授予了明确权限。请确保使用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)

- brentlightsey

---

当授予权限的对象为SCHEMA时，原来的功能无法按照预期工作。当class_desc =“SCHEMA”时，您还必须使用LEFT JOIN到sys.schemas。 - Pittsburgh DBA

Answer 4

进一步扩展 @brentlightsey 的回答，您可以添加左连接到 sys.schemas 来查看模式级别的权限：

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'

- FFFffff

Answer 5

如果您想查找某个特定用户具有权限的对象名称（如表名和存储过程），请使用以下查询语句：

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') 

- Mubeen

---

根据您发布的帖子，我建议您花些时间阅读帮助中心的文档，这样您就有最好的机会得到问题的答案。 - Brien Foss