数据库级别的角色Database-Level Roles

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

为便于管理数据库中的权限, SQL ServerSQL Server 提供了若干“角色” ,这些角色是用于对其他主体进行分组的安全主体。To easily manage the permissions in your databases, SQL ServerSQL Server provides several roles which are security principals that group other principals. 它们类似于 Windows 操作系统中的MicrosoftMicrosoftThey are like groups in the MicrosoftMicrosoft Windows operating system. 数据库级角色的权限作用域为数据库范围。Database-level roles are database-wide in their permissions scope.

若要向数据库角色添加和删除成员,请使用 ADD MEMBER ALTER ROLE DROP MEMBER 语句的 选项。To add and remove users to a database role, use the ADD MEMBER and DROP MEMBER options of the ALTER ROLE statement. 并行数据仓库Parallel Data Warehouse 和 Azure Synapse 不支持这样使用 ALTER ROLEand Azure Synapse does not support this use of ALTER ROLE. 改为使用较早版本的 sp_addrolemembersp_droprolemember 过程。Use the older sp_addrolemember and sp_droprolemember procedures instead.

存在两种类型的数据库级角色:数据库中预定义的“固定数据库角色”和可以创建的“用户定义的数据库角色”。There are two types of database-level roles: fixed-database roles that are predefined in the database and user-defined database roles that you can create.

固定数据库角色是在数据库级别定义的,并且存在于每个数据库中。Fixed-database roles are defined at the database level and exist in each database. db_owner 数据库角色的成员可以管理固定数据库角色成员身份。Members of the db_owner database role can manage fixed-database role membership. msdb 数据库中还有一些特殊用途的数据库角色。There are also some special-purpose database roles in the msdb database.

可以向数据库级角色中添加任何数据库帐户和其他 SQL ServerSQL Server 角色。You can add any database account and other SQL ServerSQL Server roles into database-level roles.

提示

请不要将用户定义的数据库角色添加为固定角色的成员。Do not add user-defined database roles as members of fixed roles. 这会导致意外的权限升级。This could enable unintended privilege escalation.

可以使用 GRANT、DENY 和 REVOKE 语句自定义用户定义数据库角色的权限。The permissions of user-defined database roles can be customized by using the GRANT, DENY, and REVOKE statements. 有关详细信息,请参阅 权限(数据库引擎)For more information, see Permissions (Database Engine).

有关所有权限的列表,请参阅 数据库引擎权限 招贴。For a list of all the permissions, see the Database Engine Permissions poster. 不能向数据库角色授予服务器级别权限。Server-level permissions cannot be granted to database roles. 不能向数据库角色添加登录名和其他服务器级别主体(如服务器角色)。Logins and other server-level principals (such as server roles) cannot be added to database roles. 对于 SQL ServerSQL Server中的服务器级别安全性,请改为使用 服务器角色For server-level security in SQL ServerSQL Server, use server roles instead. 不能通过 SQL 数据库SQL Database 和 Azure Synapse 中的角色授予服务器级别权限。Server-level permissions cannot be granted through roles in SQL 数据库SQL Database and Azure Synapse.

固定数据库角色Fixed-Database Roles

下表显示了固定数据库角色及其能够执行的操作。The following table shows the fixed-database roles and their capabilities. 所有数据库中都有这些角色。These roles exist in all databases. 无法更改分配给固定数据库角色的权限,“公共”数据库角色除外。Except for the public database role, the permissions assigned to the fixed-database roles cannot be changed.

固定数据库角色名Fixed-Database role name 说明Description
db_ownerdb_owner db_owner 固定数据库角色的成员可以执行数据库的所有配置和维护活动,还可以删除 SQL ServerSQL Server中的数据库。Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL ServerSQL Server. (在 SQL 数据库SQL Database 和 Synapse Analytics 中,某些维护活动需要服务器级别权限,并且不能由 db_owners 执行。)(In SQL 数据库SQL Database and Azure Synapse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)
db_securityadmindb_securityadmin db_securityadmin 固定数据库角色的成员可以仅修改自定义角色的角色成员资格和管理权限。Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. 此角色的成员可能会提升其权限,应监视其操作。Members of this role can potentially elevate their privileges and their actions should be monitored.
db_accessadmindb_accessadmin db_accessadmin 固定数据库角色的成员可以为 Windows 登录名、Windows 组和 SQL ServerSQL Server 登录名添加或删除数据库访问权限。Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL ServerSQL Server logins.
db_backupoperatordb_backupoperator db_backupoperator 固定数据库角色的成员可以备份数据库。Members of the db_backupoperator fixed database role can back up the database.
db_ddladmindb_ddladmin db_ddladmin 固定数据库角色的成员可以在数据库中运行任何数据定义语言 (DDL) 命令。Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriterdb_datawriter db_datawriter 固定数据库角色的成员可以在所有用户表中添加、删除或更改数据。Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareaderdb_datareader db_datareader 固定数据库角色的成员可以从所有用户表中读取所有数据。Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriterdb_denydatawriter db_denydatawriter 固定数据库角色的成员不能添加、修改或删除数据库内用户表中的任何数据。Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareaderdb_denydatareader db_denydatareader 固定数据库角色的成员不能读取数据库内用户表中的任何数据。Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

无法更改分配给固定数据库角色的权限。The permissions assigned to the fixed-database roles cannot be changed. 下图显示了分配给固定数据库角色的权限:The following figure shows the permissions assigned to the fixed-database roles:

fixed_database_role_permissions

SQL 数据库SQL Database 和 Azure Synapse 的特殊角色Special Roles for SQL 数据库SQL Database and Azure Synapse

这些数据库角色仅存在于虚拟 master 数据库中。These database roles exist only in the virtual master database. 他们的权限仅限于在 master 中执行的操作。Their permissions are restricted to actions performed in master. 只能向这些角色添加 master 中的数据库用户。Only database users in master can be added to these roles. 无法向这些角色添加登录名,但可以基于登录名创建用户,然后向角色添加用户。Logins cannot be added to these roles, but users can be created based on logins and then those users can be added to the roles. 也可以向这些角色添加 master 中包含的数据库用户。Contained database users in master can also be added to these roles. 不过,如果向 master 中的 dbmanager 角色添加包含的数据库用户,这些用户无法用于新建数据库。However, contained database users added to the dbmanager role in master cannot be used to create new databases.

角色名称Role name 说明Description
dbmanagerdbmanager 可以创建和删除数据库。Can create and delete databases. 创建数据库的 dbmanager 角色的成员成为相应数据库的所有者,这样可便于用户以 dbo 用户身份连接到相应数据库。A member of the dbmanager role that creates a database, becomes the owner of that database which allows that user to connect to that database as the dbo user. Dbo 用户具有数据库中的所有数据库权限。The dbo user has all database permissions in the database. Dbmanager 角色的成员不一定具有访问非他们所有的数据库的权限。Members of the dbmanager role do not necessarily have permission to access databases that they do not own.
loginmanagerloginmanager 可以创建和删除虚拟 master 数据库中的登录名。Can create and delete logins in the virtual master database.

备注

服务器级别主体和 Azure Active Directory 管理员(如果已配置)具有 SQL 数据库SQL Database 和 Azure Synapse 中的所有权限,且无需成为任何角色的成员。The server-level principal and the Azure Active Directory Administrator (if configured) have all permissions in the SQL 数据库SQL Database and Azure Synapse without needing to be members of any roles. 有关详细信息,请参阅 SQL 数据库身份验证和授权:授予访问权限For more information, see SQL Database Authentication and Authorization: Granting Access.

部分数据库角色不适用于 Azure SQL 或 Synapse SQL:Some database roles are not applicable to Azure SQL or Synapse SQL:

  • db_backupoperator 不适用于 Azure SQL 数据库(非托管实例)和 Synapse SQL 无服务器池,因为 T-SQL 备份和还原命令不可用。db_backupoperator is not applicable in Azure SQL database (not managed instance) and Synapse SQL serverless pool because backup and restore T-SQL comands are not available.
  • db_datawriter 和 db_denydatawriter 不适用于 Synapse SQL 无服务器,因为它只读取外部数据 。db_datawriter and db_denydatawriter are not applicable to Synapse SQL serverless because it just reads external data.

msdb 角色msdb Roles

msdb 数据库中包含下表显示的特殊用途的角色。The msdb database contains the special-purpose roles that are shown in the following table.

msdb 角色名称msdb role name 说明Description
db_ssisadmindb_ssisadmin

db_ssisoperatordb_ssisoperator

db_ssisltduserdb_ssisltduser
这些数据库角色的成员可以管理和使用 SSISSSISMembers of these database roles can administer and use SSISSSIS. 从早期版本升级的 SQL ServerSQL Server 实例可能包含使用 Data Transformation Services (DTS)(而不是 SSISSSIS)命名的旧版本角色。Instances of SQL ServerSQL Server that are upgraded from an earlier version might contain an older version of the role that was named using Data Transformation Services (DTS) instead of SSISSSIS. 有关详细信息,请参阅 Integration Services Roles(SSIS 服务)For more information, see Integration Services Roles (SSIS Service).
dc_admindc_admin

dc_operatordc_operator

dc_proxydc_proxy
这些数据库角色的成员可以管理和使用数据收集器。Members of these database roles can administer and use the data collector. 有关详细信息,请参阅 Data CollectionFor more information, see Data Collection.
PolicyAdministratorRolePolicyAdministratorRole db_ PolicyAdministratorRole 数据库角色的成员可以对基于策略的管理策略和条件执行所有配置和维护活动。Members of the db_ PolicyAdministratorRole database role can perform all configuration and maintenance activities on Policy-Based Management policies and conditions. 有关详细信息,请参阅 使用基于策略的管理来管理服务器For more information, see Administer Servers by Using Policy-Based Management.
ServerGroupAdministratorRoleServerGroupAdministratorRole

ServerGroupReaderRoleServerGroupReaderRole
这些数据库角色的成员可以管理和使用注册的服务器组。Members of these database roles can administer and use registered server groups.
dbm_monitordbm_monitor 在数据库镜像监视器中注册第一个数据库时在 msdb 数据库中创建。Created in the msdb database when the first database is registered in Database Mirroring Monitor. 在系统管理员为 dbm_monitor 角色分配用户之前,该角色没有任何成员。The dbm_monitor role has no members until a system administrator assigns users to the role.

重要

db_ssisadmin 角色和 dc_admin 角色的成员可以将其特权提升为 sysadmin。Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. 因为这些角色可以修改 Integration ServicesIntegration Services 包,而 Integration ServicesIntegration Services 使用 SQL ServerSQL Server 代理的 sysadmin 安全上下文可以执行 SQL ServerSQL Server 包,所以可以实现特权提升。This elevation of privilege can occur because these roles can modify Integration ServicesIntegration Services packages and Integration ServicesIntegration Services packages can be executed by SQL ServerSQL Server using the sysadmin security context of SQL ServerSQL Server Agent. 若要防止在运行维护计划、数据收集组和其它 Integration ServicesIntegration Services 包时提升特权,请将运行包的 SQL ServerSQL Server 代理作业配置为具有有限特权的代理帐户,或仅将 sysadmin 成员添加到 db_ssisadmindc_admin 角色。To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration ServicesIntegration Services packages, configure SQL ServerSQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.

使用数据库级角色Working with Database-Level Roles

下表说明了用于数据库级角色的命令、视图和函数。The following table explains the commands, views and functions for working with database-level roles.

FeatureFeature 类型Type 说明Description
sp_helpdbfixedrole (Transact-SQL)sp_helpdbfixedrole (Transact-SQL) 元数据Metadata 返回固定数据库角色的列表。Returns a list of the fixed database roles.
sp_dbfixedrolepermission (Transact-SQL)sp_dbfixedrolepermission (Transact-SQL) 元数据Metadata 显示固定数据库角色的权限。Displays the permissions of a fixed database role.
sp_helprole (Transact-SQL)sp_helprole (Transact-SQL) 元数据Metadata 返回当前数据库中有关角色的信息。Returns information about the roles in the current database.
sp_helprolemember (Transact-SQL)sp_helprolemember (Transact-SQL) 元数据Metadata 返回有关当前数据库中某个角色的成员的信息。Returns information about the members of a role in the current database.
sys.database_role_members (Transact-SQL)sys.database_role_members (Transact-SQL) 元数据Metadata 为每个数据库角色的每个成员返回一行。Returns one row for each member of each database role.
IS_MEMBER (Transact-SQL)IS_MEMBER (Transact-SQL) 元数据Metadata 指示当前用户是否为指定 Microsoft Windows 组或 Microsoft SQL Server 数据库角色的成员。Indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.
CREATE ROLE (Transact-SQL)CREATE ROLE (Transact-SQL) CommandCommand 在当前数据库中创建新的数据库角色。Creates a new database role in the current database.
ALTER ROLE (Transact-SQL)ALTER ROLE (Transact-SQL) CommandCommand 更改数据库角色的名称或成员身份。Changes the name or membership of a database role.
DROP ROLE (Transact-SQL)DROP ROLE (Transact-SQL) CommandCommand 从数据库中删除角色。Removes a role from the database.
sp_addrole (Transact-SQL)sp_addrole (Transact-SQL) CommandCommand 在当前数据库中创建新的数据库角色。Creates a new database role in the current database.
sp_droprole (Transact-SQL)sp_droprole (Transact-SQL) CommandCommand 从当前数据库中删除数据库角色。Removes a database role from the current database.
sp_addrolemember (Transact-SQL)sp_addrolemember (Transact-SQL) CommandCommand 为当前数据库中的数据库角色添加数据库用户、数据库角色、Windows 登录名或 Windows 组。Adds a database user, database role, Windows login, or Windows group to a database role in the current database. 并行数据仓库Parallel Data Warehouse 和 Azure Synapse 外,所有平台都应改为使用 ALTER ROLEAll platforms except 并行数据仓库Parallel Data Warehouse and Azure Synapse should use ALTER ROLE instead.
sp_droprolemember (Transact-SQL)sp_droprolemember (Transact-SQL) CommandCommand 从当前数据库的 SQL Server 角色中删除安全帐户。Removes a security account from a SQL Server role in the current database. 并行数据仓库Parallel Data Warehouse 和 Azure Synapse 外,所有平台都应改为使用 ALTER ROLEAll platforms except 并行数据仓库Parallel Data Warehouse and Azure Synapse should use ALTER ROLE instead.
GRANTGRANT 权限Permissions 向角色添加权限。Adds permission to a role.
DENYDENY 权限Permissions 拒绝向角色授予权限。Denies a permission to a role.
REVOKEREVOKE 权限Permissions 撤消以前授予或拒绝的权限。Removes a previously granted or denied permissions.

public 数据库角色public Database Role

每个数据库用户都属于 public 数据库角色。Every database user belongs to the public database role. 如果未向某个用户授予或拒绝对安全对象的特定权限时,该用户将继承授予该对象的 public 角色的权限。When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. 无法将数据库用户从 public 角色删除。Database users cannot be removed from the public role.

安全性目录视图 (Transact-SQL)Security Catalog Views (Transact-SQL)

安全存储过程 (Transact-SQL)Security Stored Procedures (Transact-SQL)

安全函数 (Transact-SQL)Security Functions (Transact-SQL)

保护 SQL ServerSecuring SQL Server

sp_helprotect (Transact-SQL)sp_helprotect (Transact-SQL)