用于权限管理的 Azure SQL 数据库服务器角色

项目
04/08/2024

适用于：Azure SQL 数据库

本文介绍了 Azure SQL 数据库中的固定服务器级别角色。

注意

本文中的固定服务器级别角色以公共预览版向 Azure SQL 数据库提供。这些服务器级角色也是 SQL Server 2022 版本的一部分。

概述

在 Azure SQL 数据库中，服务器是一个逻辑概念，不能在服务器级别授予权限。为了简化权限管理，Azure SQL 数据库提供了一组固定的服务器级角色来帮助管理逻辑服务器上的权限。角色是对登录进行分组的安全主体。

注意

本文中所说的“角色”概念类似于 Windows 操作系统中的“组”。

这些特殊的固定服务器级角色使用前缀 ##MS_ 和后缀 ## 来区分其他常规用户创建的主体。

与本地 SQL Server 一样，服务器权限也是分层组织的。这些服务器级角色拥有的权限可以传播到数据库权限。若要使权限在数据库级别有效地发挥作用，登录需要是服务器级别角色 ##MS_DatabaseConnector##（该角色授予对所有数据库的 CONNECT 权限）的成员，或在各个数据库中拥有用户帐户。这也适用于虚拟 master 数据库。

例如，服务器级角色 ##MS_ServerStateReader## 保留权限 VIEW SERVER STATE。如果作为此角色成员的登录在 master 和 WideWorldImporters 数据库中具有用户帐户，则该用户将在这两个数据库中拥有 VIEW DATABASE STATE 权限。

注意

可以在用户数据库中拒绝任何权限，效果等同于通过角色成员资格覆盖服务器范围的授权。但是，在系统数据库 master 中，不能授予或拒绝权限。

Azure SQL 数据库目前提供七个固定服务器角色。无法更改被授予了固定服务器角色的权限，并且这些角色不能以其他固定角色作为成员。可以将服务器级别的登录名作为成员添加到服务器级别角色。

重要

固定服务器角色的每个成员都可以将其他登录名添加到该同一角色。

有关 Azure SQL 数据库登录名和用户的详细信息，请参阅向数据库授予对 Azure SQL 数据库、SQL 托管实例和 Azure Synapse Analytics 的访问权限。

服务器级别的固定角色

下表显示了服务器级的固定角色及其权限。

服务器级的固定角色	说明
`##MS_DatabaseConnector##`	`##MS_DatabaseConnector##` 固定服务器角色的成员可以连接到任何数据库，而无需数据库中的用户帐户进行连接。若要拒绝对特定数据库的 `CONNECT` 权限，用户可在数据库中为此登录创建匹配的用户帐户，然后 `DENY` 对数据库用户的 `CONNECT` 权限。此 `DENY` 权限将推翻来自此角色的 `GRANT CONNECT` 权限。
`##MS_DatabaseManager##`	`##MS_DatabaseManager##` 固定服务器角色的成员可以创建和删除数据库。创建数据库的 `##MS_DatabaseManager##` 角色的成员成为相应数据库的所有者，这样该用户就能够以 `dbo` 用户身份连接到相应数据库。 `dbo` 用户具有数据库中的所有数据库权限。 `##MS_DatabaseManager##` 角色的成员不一定具有访问非他们所有的数据库的权限。建议对 `master` 中存在的 dbmanager 数据库级角色使用此服务器角色。
`##MS_DefinitionReader##`	`##MS_DefinitionReader##` 固定服务器角色的成员可以读取 `VIEW ANY DEFINITION` 覆盖的所有目录视图，分别是在该角色的成员具有用户帐户的任何数据库上 `VIEW DEFINITION`。
`##MS_LoginManager##`	`##MS_LoginManager##` 固定服务器角色的成员可以创建和删除登录名。建议对 `master` 中存在的 loginmanager 数据库级角色使用此服务器角色。
`##MS_SecurityDefinitionReader##`	`##MS_SecurityDefinitionReader##` 固定服务器角色的成员可以读取 `VIEW ANY SECURITY DEFINITION` 覆盖的所有目录视图，分别在该角色的成员具有用户帐户的任何数据库上具有 `VIEW SECURITY DEFINITION` 权限。这是 `##MS_DefinitionReader##` 服务器角色有权访问的内容的一小部分。
`##MS_ServerStateManager##`	`##MS_ServerStateManager##` 固定服务器角色的成员与 `##MS_ServerStateReader##` 角色具有相同的权限。此外，该角色还具有 `ALTER SERVER STATE` 权限，该权限允许访问多个管理操作，例如 `DBCC FREEPROCCACHE`、`DBCC FREESYSTEMCACHE ('ALL')`、`DBCC SQLPERF()`；
`##MS_ServerStateReader##`	`##MS_ServerStateReader##` 固定服务器角色的成员可以读取 `VIEW SERVER STATE` 所涵盖的所有动态管理视图 (DMV) 和功能，分别是该角色的成员具有用户帐户的任何数据库上的 `VIEW DATABASE STATE`。

固定服务器角色的权限

每个固定服务器级别角色都被分配了特定的权限。下表显示了分配给服务器级角色的权限。它还显示继承的数据库级权限，前提是用户可以连接到单个数据库。

服务器级的固定角色	服务器级别权限	数据库级别权限（如果存在与登录名匹配的数据库用户）
`##MS_DatabaseConnector##`	`CONNECT ANY DATABASE`	`CONNECT`
`##MS_DatabaseManager##`	`CREATE ANY DATABASE`、`ALTER ANY DATABASE`	`ALTER`
`##MS_DefinitionReader##`	`VIEW ANY DATABASE`、`VIEW ANY DEFINITION`、`VIEW ANY SECURITY DEFINITION`	`VIEW DEFINITION`、`VIEW SECURITY DEFINITION`
`##MS_LoginManager##`	`CREATE LOGIN`、`ALTER ANY LOGIN`	空值
`##MS_SecurityDefinitionReader##`	`VIEW ANY SECURITY DEFINITION`	`VIEW SECURITY DEFINITION`
`##MS_ServerStateManager##`	`ALTER SERVER STATE`、`VIEW SERVER STATE`、`VIEW SERVER PERFORMANCE STATE`、`VIEW SERVER SECURITY STATE`	`VIEW DATABASE STATE`、`VIEW DATABASE PERFORMANCE STATE`、`VIEW DATABASE SECURITY STATE`
`##MS_ServerStateReader##`	`VIEW SERVER STATE`、`VIEW SERVER PERFORMANCE STATE`、`VIEW SERVER SECURITY STATE`	`VIEW DATABASE STATE`、`VIEW DATABASE PERFORMANCE STATE`、`VIEW DATABASE SECURITY STATE`

权限

只有服务器管理员帐户或 Microsoft Entra 管理员帐户（可以是 Microsoft Entra 组）才能向服务器角色添加或删除其他登录名。此内容特定于 Azure SQL 数据库。

注意

Microsoft Entra ID 以前称为 Azure Active Directory (Azure AD)。

使用服务器级角色

下表介绍了可以 Azure SQL 数据库中可用于服务器级角色的系统视图和功能。

功能	类型	描述
IS_SRVROLEMEMBER	元数据	指示 SQL 登录名是否为指定服务器级角色的成员。
sys.server_role_members	元数据	为每个服务器级角色的每个成员返回一行。
sys.sql_logins	元数据	为每个 SQL 登录名返回一行。
ALTER SERVER ROLE	命令	更改服务器角色的成员资格。

示例

本节中的示例展示了如何在 Azure SQL 数据库中使用服务器级角色。

以下示例将 SQL 登录名 Jiao 添加到 ##MS_ServerStateReader## 服务器级别角色中。该语句必须在虚拟 master 数据库中运行。

ALTER SERVER ROLE ##MS_ServerStateReader##
    ADD MEMBER Jiao;
GO

B. 列出作为服务器级角色成员的所有主体（SQL 身份验证）

以下语句使用 sys.server_role_members 和 sys.sql_logins 目录视图返回任何固定服务器级角色的所有成员。该语句必须在虚拟 master 数据库中运行。

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

第 1 部分：准备角色成员资格和用户帐户

从虚拟 master 数据库运行此命令。

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER Jiao;

-- check membership in metadata:
SELECT IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao');
--> 1 = Yes

SELECT sql_logins.principal_id AS MemberPrincipalID,
    sql_logins.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id;
GO

结果集如下。

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName
------------- ------------- ------------------ -----------
6         Jiao      11            ##MS_ServerStateReader##

从用户数据库运行此命令。

-- Create a database-User for 'Jiao'
CREATE USER Jiao
FROM LOGIN Jiao;
GO

第 2 部分：测试角色成员资格

以登录名 Jiao 身份登录并连接到示例中使用的用户数据库。

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server');

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##');
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE');
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

-- example query:
SELECT * FROM sys.dm_exec_query_stats;
--> will return data since this user has the necessary permission

D. 检查 Microsoft Entra 登录名的服务器级别角色

在虚拟 master 数据库中运行此命令，以查看所有属于 SQL 数据库中服务器级别角色的 Microsoft Entra 登录名。有关 Microsoft Entra 服务器登录名的详细信息，请参阅 Microsoft Entra 服务器主体。

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

E. 检查虚拟 `master` 数据库角色中的特定登录名

在虚拟 master 数据库中运行此命令，以检查 bob 具有的角色，或更改该值以匹配主体。

SELECT DR1.name AS DbRoleName,
    ISNULL(DR2.name, 'No members') AS DbUserName
FROM sys.database_role_members AS DbRMem
RIGHT JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id
LEFT JOIN sys.database_principals AS DR2
    ON DbRMem.member_principal_id = DR2.principal_id
WHERE DR1.type = 'R'
    AND DR2.name LIKE 'bob%';

服务器级角色的限制

角色分配最多可能需要 5 分钟才能生效。同样对于现有会话，在关闭并重新打开连接之前，对服务器角色分配的更改不会生效。这是由于 master 数据库和同一逻辑服务器上的其他数据库之间的分布式架构造成的。
- 部分解决方法：为了缩短等待期并确保数据库中的服务器角色分配是最新的，服务器管理员或 Azure AD 管理员可以在登录名有权访问的用户数据库中运行 DBCC FLUSHAUTHCACHE。当前登录的用户在运行 DBCC FLUSHAUTHCACHE 后仍需要重新连接，以使成员资格更改对这些用户生效。
master 数据库中不支持 IS_SRVROLEMEMBER()。

用于权限管理的 Azure SQL 数据库服务器角色

概述

服务器级别的固定角色

固定服务器角色的权限

权限

使用服务器级角色

示例

A. 向服务器级角色添加 SQL 登录名

B. 列出作为服务器级角色成员的所有主体（SQL 身份验证）

°C 完整示例：向服务器级角色添加登录名，检索角色成员资格和权限的元数据，并运行测试查询

第 1 部分：准备角色成员资格和用户帐户

第 2 部分：测试角色成员资格

D. 检查 Microsoft Entra 登录名的服务器级别角色

E. 检查虚拟 master 数据库角色中的特定登录名

服务器级角色的限制

相关内容

其他资源

E. 检查虚拟 `master` 数据库角色中的特定登录名