資料庫層級角色

適用于:yesSQL Server (所有支援的版本 Yes) Azure SQL Database Yes Azure SQL 受控執行個體 yes Azure Synapse Analytics Analytics yes Platform System (PDW)

若要輕鬆地管理資料庫中的許可權,SQL Server提供數個*角色,這些角色是將其他主體分組的安全性主體。 它們就像是 Microsoft Windows 作業系統中的群組。 資料庫層級角色的權限範圍為整個資料庫。

若要新增和移除資料庫角色的使用者,請使用 ADD MEMBER ALTER ROLE DROP MEMBER 陳述式的 選項。 Analytics Platform System (PDW) 和 Azure Synapse 不支援這種用法 ALTER ROLE 。 請改用舊版的 sp_addrolemembersp_droprolemember 程序。

資料庫層級角色類型有兩種:在資料庫中預先定義的「固定資料庫角色」 以及您可以建立的「使用者定義資料庫角色」 。

固定資料庫角色義於資料庫層級,並存在每個資料庫中。 db_owner 資料庫角色的成員可以管理固定的資料庫角色成員資格。 在 msdb 資料庫中,也有一些特殊用途的資料庫角色。

您可以將任何資料庫帳戶和其他SQL Server角色新增至資料庫層級角色。

提示

請勿將使用者定義資料庫角色當作固定角色成員加入。 這樣會產生不必要的權限擴大。

您可以使用 GRANTDENYREVOKE 語句來自訂使用者定義資料庫角色的許可權。 如需詳細資訊,請參閱 權限 (Database Engine)

如需所有權限的清單,請參閱 Database Engine 權限 海報。 資料庫角色不能獲派伺服器層級權限。 登入和其他伺服器層級主體 (例如伺服器角色) 無法加入資料庫角色。 針對 SQL Server 中的伺服器層級安全性,請改用伺服器角色。 伺服器層級許可權無法透過 SQL Database 和 Azure Synapse 中的角色授與。

固定資料庫角色

下表顯示固定資料庫角色及其功能。 這些角色存在所有資料庫中。 除了公用資料庫角色外,指派給固定資料庫角色的權限無法變更。

固定資料庫角色名稱 描述
db_owner db_owner固定資料庫角色的成員可以在資料庫上執行所有組態和維護活動,也可以 drop SQL Server中的資料庫。 (在SQL Database和Azure Synapse中,某些維護活動需要伺服器層級許可權,且無法由db_owners.) 執行
db_securityadmin db_securityadmin 固定資料庫角色的成員可以修改角色成員資格 (僅自訂角色) 以及管理權限。 此角色的成員可能會提升其權限,因此其動作應受到監視。
db_accessadmin db_accessadmin固定資料庫角色的成員可以針對Windows登入、Windows群組和SQL Server登入新增或移除資料庫的存取權。
db_backupoperator db_backupoperator 固定資料庫角色的成員可以備份資料庫。
db_ddladmin db_ddladmin 固定資料庫角色的成員可在資料庫中執行任何「資料定義語言」(DDL) 的命令。
db_datawriter db_datawriter 固定資料庫角色的成員可以加入、刪除或變更所有使用者資料表中的資料。
db_datareader db_datareader固定資料庫角色的成員可以從所有使用者資料表和檢視讀取所有資料。 除了 sysINFORMATION_SCHEMA以外,使用者物件可以存在於任何架構中。
db_denydatawriter db_denydatawriter 固定資料庫角色的成員不能加入、修改或刪除資料庫中使用者資料表的任何資料。
db_denydatareader db_denydatareader固定資料庫角色的成員無法從資料庫內的使用者資料表和檢視讀取任何資料。

指派給固定資料庫角色的權限無法變更。 下圖顯示指派給固定資料庫角色的權限:

fixed_database_role_permissions

SQL Database和Azure Synapse的特殊角色

這些資料庫角色只存在於虛擬 master 資料庫中。 其權限僅限於能在 master 中執行的動作。 只有 master 資料庫使用者可以加入這些角色中。 這些角色中不能加入登入,但可以根據登入建立使用者,然後將這些使用者加入角色中。 包含的 master 資料庫使用者,也可加入這些角色中。 但是,加入到 dbmanager 角色的包含的 master 資料庫使用者不能用來建立新的資料庫。

角色名稱 描述
dbmanager 可以建立和刪除資料庫。 建立資料庫的 dbmanager 角色成員會成為該資料庫的擁有者,該資料庫可讓使用者以 dbo 使用者身分連接到該資料庫。 dbo 使用者具有資料庫的所有資料庫權限。 dbmanager 角色的成員不一定有權存取他們不擁有的資料庫。
db_exporter 僅適用于先前SQL DW) (專用SQL集區Azure Synapse分析。
db_exporter固定資料庫角色的成員可以執行所有資料匯出活動。 透過此角色授與的許可權包括 CREATE TABLE、ALTER ANY SCHEMA、ALTER ANY EXTERNAL DATA SOURCE、ALTER ANY EXTERNAL FILE FORMAT。
loginmanager 可以建立及刪除虛擬 master 資料庫的登入。

注意

如果設定) 在SQL Database和Azure Synapse中擁有擁有權限,則伺服器層級主體和Azure Active Directory系統管理員 (,而不需要是任何角色的成員。 如需詳細資訊,請參閱 SQL Database 驗證和授權:授與存取權

某些資料庫角色不適用於Azure SQL或Azure Synapse:

  • db_backupoperator不適用於Azure SQL資料庫 (非受控實例) 和Azure Synapse無伺服器集區,因為無法使用備份和還原 T-SQL 命令。
  • db_datawriterdb_denydatawriter不適用於無伺服器Azure Synapse,因為它只會讀取外部資料。

msdb 角色

msdb 資料庫含有下表所示的特殊用途角色。

msdb 角色名稱 描述
db_ssisadmin

db_ssisoperator

db_ssisltduser
這些資料庫角色的成員可以管理和使用 SSIS。 從舊版升級的SQL Server實例可能包含使用資料轉換服務命名的舊版角色, (DTS) 而不是 SSIS。 如需詳細資訊,請參閱 (SSIS 服務) 的 Integration Services 角色
dc_admin

dc_operator

dc_proxy
這些資料庫角色的成員可以管理和使用資料收集器。 如需相關資訊,請參閱 Data Collection
PolicyAdministratorRole db_ PolicyAdministratorRole 資料庫角色的成員可以在以原則為基礎的管理原則和條件上執行所有組態和維護活動。 如需詳細資訊,請參閱 使用原則式管理來管理伺服器
ServerGroupAdministratorRole

ServerGroupReaderRole
這些資料庫角色的成員可以管理和使用已註冊的伺服器群組。
dbm_monitor 在「資料庫鏡像監視器」中註冊第一個資料庫時,於 msdb 資料庫中建立的。 dbm_monitor 角色沒有任何成員,必須由系統管理員指派使用者給該角色。

重要

db_ssisadmin 角色和 dc_admin 角色的成員可以將其權限提高為系統管理員。 這種提高許可權可能會發生,因為這些角色可以使用 SQL Server Agent 的系統管理員安全性內容,SQL Server來執行 Integration Services 套件和 Integration Services 套件。 若要在執行維護計畫、資料收集組和其他 Integration Services 套件時,請設定執行套件的SQL Server Agent作業,以使用具有有限許可權的 Proxy 帳戶,或只將系統管理員成員新增至db_ssisadmindc_admin角色。

使用資料庫層級角色

下表說明使用資料庫層級角色的命令、檢視和函式。

功能 類型 描述
sp_helpdbfixedrole (Transact-SQL) 中繼資料 傳回固定資料庫角色的清單。
sp_dbfixedrolepermission (Transact-SQL) 中繼資料 顯示固定資料庫角色的權限。
sp_helprole (Transact-SQL) 中繼資料 傳回目前資料庫中角色的相關資訊。
sp_helprolemember (Transact-SQL) 中繼資料 傳回目前資料庫中角色成員的相關資訊。
sys.database_role_members (Transact-SQL) 中繼資料 針對每個資料庫角色的每個成員,各傳回一個資料列。
IS_MEMBER (Transact-SQL) 中繼資料 指出目前使用者是指定之 Microsoft Windows 群組或 Microsoft SQL Server 資料庫角色的成員。
CREATE ROLE (Transact-SQL) Command 在目前資料庫中建立新的資料庫角色。
ALTER ROLE (Transact-SQL) Command 變更資料庫角色的名稱或成員資格。
DROP ROLE (Transact-SQL) Command 從資料庫中移除角色。
sp_addrole (Transact-SQL) Command 在目前資料庫中建立新的資料庫角色。
sp_droprole (Transact-SQL) Command 從目前資料庫移除資料庫角色。
sp_addrolemember (Transact-SQL) Command 在目前資料庫的資料庫角色中,加入資料庫使用者、資料庫角色、Windows 登入或 Windows 群組。 除了 Analytics Platform System (PDW 以外的所有平臺,) 和Azure Synapse都應該改用 ALTER ROLE
sp_droprolemember (Transact-SQL) Command 從目前資料庫中的 SQL Server 角色移除安全性帳戶。 除了 Analytics Platform System (PDW 以外的所有平臺,) 和Azure Synapse都應該改用 ALTER ROLE
GRANT 權限 新增角色權限。
DENY 權限 拒絕角色的權限。
REVOKE 權限 移除先前授與或拒絕的許可權。

公用資料庫角色

每個資料庫使用者都屬於 public 資料庫角色。 當使用者尚未授與或拒絕安全性實體物件的特定許可權時,使用者會繼承該物件上授與給 公用 的許可權。 無法移除 公用 角色中的資料庫使用者。

範例

本節中的範例示範如何使用資料庫層級角色。

A. 將使用者新增至資料庫層級角色

下列範例會將使用者 'Ben' 新增至固定資料庫層級角色 db_datareader

ALTER ROLE db_datareader
	ADD MEMBER Ben;  
GO

B. 列出屬於資料庫層級角色成員的所有資料庫主體

下列語句會傳回任何資料庫角色的所有成員。

SELECT    roles.principal_id                            AS RolePrincipalID
    ,    roles.name                                    AS RolePrincipalName
    ,    database_role_members.member_principal_id    AS MemberPrincipalID
    ,    members.name                                AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members  
JOIN sys.database_principals AS roles  
    ON database_role_members.role_principal_id = roles.principal_id  
JOIN sys.database_principals AS members  
    ON database_role_members.member_principal_id = members.principal_id;  
GO