你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站,请访问 https://docs.azure.cn

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

注意

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

适用于: Azure SQL 数据库

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

注意

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

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

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

例如,服务器级别角色 ##MS_ServerStateReader## 具有“查看服务器状态”权限。 如果作为此角色成员的登录在 masterWideWorldImporters 数据库中具有用户帐户,则该用户将在这两个数据库中拥有“查看数据库状态”权限。

注意

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

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

重要

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

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

服务器级别的固定角色

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

服务器级的固定角色 说明
##MS_DatabaseConnector## ##MS_DatabaseConnector## 固定服务器角色的成员可连接到任何数据库,而无需数据库中的用户帐户即可连接。

若要拒绝对特定数据库的“连接”权限,用户可在数据库中为此登录创建匹配的用户帐户,然后拒绝对数据库用户的“连接”权限。 此“拒绝”权限将推翻来自此角色的“授予连接”权限。
##MS_DatabaseManager## ##MS_DatabaseManager## 固定服务器角色的成员可创建和删除数据库。 创建数据库的 ##MS_DatabaseManager## 角色的成员成为相应数据库的所有者,这样可便于用户以 dbo 用户身份连接到相应数据库。 dbo 用户具有数据库中的所有数据库权限。 ##MS_DatabaseManager## 角色的成员不一定具有访问非他们所有的数据库的权限。 建议对 master 中存在的 dbmanager 数据库级角色使用此服务器角色。
##MS_DefinitionReader## ##MS_DefinitionReader## 固定服务器角色的成员可以读取“查看任何定义”覆盖的所有目录视图,分别是在该角色的成员具有用户帐户的任何数据库上“查看定义”。
##MS_LoginManager## ##MS_LoginManager## 固定服务器角色的成员可创建和删除登录。 建议对 master 中存在的 loginmanager 数据库级角色使用此服务器角色。
##MS_SecurityDefinitionReader## ##MS_SecurityDefinitionReader## 固定服务器角色的成员可以读取“查看任何安全定义”覆盖的所有目录视图,并分别对此角色的成员拥有用户帐户的任何数据库拥有“查看安全定义”权限。 这是 ##MS_DefinitionReader## 服务器角色有权访问的内容的一小部分。
##MS_ServerStateReader## ##MS_ServerStateReader## 固定服务器角色的成员可以读取“查看服务器状态”所涵盖的所有动态管理视图 (DMV) 和功能,分别是该角色的成员具有用户帐户的任何数据库上的“查看数据库状态”。
##MS_ServerStateManager## ##MS_ServerStateManager## 固定服务器角色的成员与##MS_ServerStateReader## 角色具有相同的权限。 此外,该角色还具有“变更服务器状态”权限,该权限允许访问多个管理操作,例如:DBCC FREESYSTEMCACHE ('ALL')DBCC SQLPERF()

固定服务器角色的权限

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

服务器级的固定角色 服务器级别权限 数据库级别权限(如果存在与登录名匹配的数据库用户)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE
ALTER ANY DATABASE
ALTER
##MS_DefinitionReader## 查看任何数据库、查看任何定义、查看任何安全定义 查看定义,查看安全定义
##MS_LoginManager## CREATE LOGIN
ALTER ANY LOGIN
不适用
##MS_SecurityDefinitionReader## 查看任何安全定义 查看安全定义
##MS_ServerStateReader## 查看服务器状态、查看服务器性能状态、查看服务器安全状态 查看数据库状态、查看数据库性能状态、查看数据库安全状态
##MS_ServerStateManager## 变更服务器状态、查看服务器状态、查看服务器性能状态、查看服务器安全状态 查看数据库状态、查看数据库性能状态、查看数据库安全状态

使用服务器级角色

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

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

示例

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

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

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

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

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

以下语句使用 sys.server_role_memberssys.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##   

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

-- Creating 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. 检查服务器级角色中的 Azure AD 登录名

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

SELECT roles.principal_id AS RolePID,roles.name AS RolePName,
       server_role_members.member_principal_id AS MemberPID, members.name AS MemberPName
       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 members 
       ON server_role_members.member_principal_id = members.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 OUTER JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id LEFT OUTER 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()

另请参阅