適用於權限管理的 Azure SQL Database 伺服器角色

適用於： 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 資料庫登入和使用者的詳細資訊，請參閱授權資料庫存取 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	N/A
##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 登入傳回一個資料列。
變更伺服器角色	Command	變更伺服器角色的成員資格。

範例

本章節中的範例會示範如何處理 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 Database 中伺服器層級角色的所有 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 資料庫與相同邏輯伺服器上的其他資料庫之間的分散式架構。

  • 部份因應措施：若要減少等待期間，並確保伺服器角色指派是資料庫中的現行指派，伺服器管理員或 Microsoft Entra 系統管理員可以在登入可存取的使用者資料庫中執行 DBCC FLUSHAUTHCACHE。 目前登入的使用者在執行 DBCC FLUSHAUTHCACHE 後仍必須重新連線，成員資格變更才會生效。

• 在 IS_SRVROLEMEMBER() 資料庫中不支援 master。

相關內容

• 資料庫層級角色
• 安全性目錄檢視 (Transact-SQL)
• 安全性函數 (Transact-SQL)
• 權限 (資料庫引擎)
• DBCC FLUSHAUTHCACHE (Transact-SQL)