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

适用于：Azure SQL 数据库

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

概述

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

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

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

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

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

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

服务器级别的固定角色

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

固定服务器角色的权限

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

权限

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

使用服务器级角色

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

示例

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

A. 向服务器级角色添加 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

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

第 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()。

相关内容

• 数据库级别的角色
• 安全性目录视图 (Transact-SQL)
• 安全函数 (Transact-SQL)
• 权限（数据库引擎）
• DBCC FLUSHAUTHCACHE (Transact SQL)