版權管理 Azure SQL Database 伺服器角色

適用於: Azure SQL Database

在 Azure SQL Database 中,伺服器是邏輯概念,而且無法在伺服器層級授與許可權。 為了簡化版權管理,Azure SQL Database 提供一組固定伺服器層級角色,以協助您管理邏輯伺服器上的許可權。 角色是群組登入的安全性主體。

注意

本文中的 角色 概念就像是 Windows 作業系統中的 群組

這些特殊的固定伺服器層級角色會使用前置詞 # #MS_ 和尾碼 ## 來區別其他一般使用者建立的主體。

和內部部署 SQL Server 一樣,伺服器許可權會以階層方式組織。 這些伺服器層級角色所持有的許可權可以傳播至資料庫許可權。 若要讓許可權有效地傳播至資料庫,登入必須具有資料庫中的使用者帳戶。

例如,伺服器層級角色 # #MS_ServerStateReader # # 會保存許可權 VIEW 伺服器狀態。 如果屬於這個角色成員的登入在資料庫 masterWideWorldImporters 中有使用者帳戶,則此使用者將擁有這兩個資料庫中的 [ VIEW DATABASE STATE ] 許可權。

注意

您可以在使用者資料庫中拒絕任何許可權,如此一來,就可以透過角色成員資格覆寫伺服器範圍授與。 不過,在系統資料庫 主機 中,無法授與或拒絕許可權。

Azure SQL Database 目前提供三個固定伺服器角色。 授與固定伺服器角色的許可權無法變更,且這些角色不能有其他固定角色做為成員。 您可以將伺服器層級的 SQL 登入加入至伺服器層級角色的成員。

重要

固定伺服器角色的每個成員可以對相同的角色增加其他登入。

如需 Azure SQL Database 登入和使用者的詳細資訊,請參閱授權資料庫存取 SQL Database、SQL 受控執行個體和 Azure Synapse Analytics

內建的伺服器層級角色

下表顯示固定伺服器層級角色及其功能。

內建伺服器層級角色 描述
# #MS_DefinitionReader## # #MS_DefinitionReader # # 固定伺服器角色的成員可以讀取所有在此角色的成員具有使用者帳戶之 資料庫上的 view ANY definition 所涵蓋的所有目錄檢視。
# #MS_ServerStateReader## # #MS_ServerStateReader # # 固定伺服器角色的成員可以讀取所有動態管理檢視 (dmv) 和 view server STATE 所涵蓋的函式,分別查看此角色的成員具有使用者帳戶之任何資料庫上的 資料庫狀態
# #MS_ServerStateManager## # #MS_ServerStateManager # # 固定伺服器角色的成員與 # #MS_ServerStateReader # # 角色具有相同的許可權。 此外,它還保有 ALTER SERVER STATE 許可權,以允許存取數個管理作業,例如: DBCC FREEPROCCACHEDBCC FREESYSTEMCACHE ('ALL') 、、 DBCC SQLPERF()

固定伺服器角色的許可權

每個內建的伺服器層級角色都有指派的特定許可權。 下表顯示指派給伺服器層級角色的許可權。 如果資料庫中有使用者帳戶,它也會顯示繼承的資料庫層級許可權。

固定伺服器層級角色 伺服器層級權限 資料庫層級許可權 (是否存在符合登入的資料庫使用者)
# #MS_DefinitionReader## 查看任何資料庫、查看任何定義、查看任何安全性定義 視圖定義,視圖安全性定義
# #MS_ServerStateReader## VIEW SERVER STATE、VIEW SERVER PERFORMANCE STATE、VIEW SERVER SECURITY STATE 視圖資料庫狀態,視圖資料庫效能狀態,視圖資料庫安全性狀態
# #MS_ServerStateManager## ALTER SERVER STATE、VIEW SERVER STATE、VIEW SERVER PERFORMANCE STATE、VIEW SERVER SECURITY STATE 視圖資料庫狀態,視圖資料庫效能狀態,視圖資料庫安全性狀態

使用伺服器層級角色

下表說明系統檢視,以及您可以用來在 Azure SQL Database 中處理伺服器層級角色的函數。

功能 類型 描述
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 Database 中的伺服器層級角色。

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##   

從使用者資料庫執行此命令。

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

第2部分:測試角色成員資格

以 login 的身分登入 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

伺服器層級角色的限制

  • 角色指派最多可能需要5分鐘的時間才會生效。 此外,對於現有的會話,伺服器角色指派的變更在關閉並重新開啟連接之前不會生效。 這是因為 master 資料庫與相同邏輯伺服器上的其他資料庫之間的分散式架構。

    • 部分因應措施:若要縮短等候期間,並確保伺服器角色指派是資料庫中的最新狀態,則伺服器管理員或 Azure AD 管理員可以 DBCC FLUSHAUTHCACHE 在登入具有存取權的使用者資料庫 (s) 中執行。 目前登入的使用者在執行之後仍必須重新連線, DBCC FLUSHAUTHCACHE 才能讓成員資格變更生效。
  • IS_SRVROLEMEMBER()master 資料庫不支援。

另請參閱