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

  • 發行項

適用於：Azure SQL Database

本文說明 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特定資料庫的許可權，用戶可以為資料庫中的這個登入建立相符的用戶帳戶，然後DENYCONNECT建立資料庫用戶的許可權。 此 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 DATABASE STATE讀取所涵蓋VIEW SERVER STATE的所有動態管理檢視（DMV）和函式，以及此角色成員具有用戶帳戶的任何資料庫。

固定伺服器角色的權限

每個固定伺服器層級角色都有指派給它的特定許可權。 下表顯示指派給伺服器層級角色的權限。 它也會顯示資料庫層級的許可權，只要使用者可以連線到個別資料庫，就會繼承這些許可權。

固定伺服器層級角色 伺服器層級權限 資料庫層級權限（如果有符合登入的資料庫使用者存在）
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE, ALTER ANY DATABASE ALTER
##MS_DefinitionReader## VIEW ANY DATABASE、 、 VIEW ANY DEFINITIONVIEW 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 STATEVIEW SERVER SECURITY STATE VIEW DATABASE STATE、 、 VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE
##MS_ServerStateReader## VIEW SERVER STATE、 、 VIEW SERVER PERFORMANCE STATEVIEW SERVER SECURITY STATE VIEW DATABASE STATE、 、 VIEW DATABASE PERFORMANCE STATEVIEW DATABASE SECURITY STATE

權限

只有伺服器管理員帳戶或 Microsoft Entra 系統管理員帳戶（可以是 Microsoft Entra 群組）可以新增或移除伺服器角色的其他登入。 這是 Azure SQL 資料庫 特有的。

注意

Microsoft Entra 標識符 先前稱為 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 資料庫 中伺服器層級角色的所有 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 。

相關內容