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

2021-12-06 00:00:00 azure-sql-database azure sql-server azure-sql

您能否指导我如何查看授予 Azure SQL 数据库中的任何数据库用户或一般的 MSSQL Server 实例的当前角色/权限?

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?

我有以下查询:

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"的权限是什么?

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

你能帮我解决这个问题吗?

Could you help me on this?

推荐答案

根据 sys.database_permissions，此查询列出了您所连接的数据库中的主体明确授予或拒绝的所有权限:

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;

根据 管理 Azure SQL 数据库中的数据库和登录， loginmanager 和 dbmanager 角色是 Azure SQL 数据库中可用的两个服务器级安全角色.loginmanager 角色有创建登录的权限，dbmanager 角色有创建数据库的权限.您可以使用上面针对 ma​​ster 数据库的查询来查看哪些用户属于这些角色.您还可以在连接到每个用户数据库时使用相同的查询(减去过滤谓词)来确定用户在每个用户数据库上的角色成员资格.

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.

相关文章