如何查看在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?

我在下面的查询中有此内容:

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

我还需要知道授予这些角色"loginmanager"和"dbmanager"的权限是什么?

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

您能帮我吗?

推荐答案

对于 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角色具有创建数据库的权限.您可以使用上面针对 master 数据库的查询来查看哪些用户属于这些角色.您还可以通过在连接到用户数据库时使用相同的查询(减去过滤谓词)来确定用户在每个用户数据库上的角色成员身份.

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.

这篇关于如何查看在Azure SQL服务器实例中授予任何数据库用户的角色和权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！