服务器级别角色Server-Level Roles

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SQL ServerSQL Server 提供服务器级角色以帮助你管理服务器上的权限。provides server-level roles to help you manage the permissions on a server. 这些角色是可组合其他主体的安全主体。These roles are security principals that group other principals. 服务器级角色的权限作用域为服务器范围。Server-level roles are server-wide in their permissions scope. (“角色” 类似于 Windows 操作系统中的“组” 。)(Roles are like groups in the Windows operating system.)

提供固定服务器角色是为了方便使用和向后兼容。Fixed server roles are provided for convenience and backward compatibility. 应尽可能分配更具体的权限。Assign more specific permissions whenever possible.

SQL ServerSQL Server 提供了九种固定服务器角色。provides nine fixed server roles. 无法更改授予固定服务器角色(public 角色除外)的权限 。The permissions that are granted to the fixed server roles (except public) cannot be changed. SQL Server 2012 (11.x)SQL Server 2012 (11.x)开始,您可以创建用户定义的服务器角色,并将服务器级权限添加到用户定义的服务器角色。Beginning with SQL Server 2012 (11.x)SQL Server 2012 (11.x), you can create user-defined server roles and add server-level permissions to the user-defined server roles.

可以将服务器级别主体(SQL ServerSQL Server 登录名、Windows 帐户和 Windows 组)添加到服务器级别角色。You can add server-level principals ( SQL ServerSQL Server logins, Windows accounts, and Windows groups) into server-level roles. 固定服务器角色的每个成员都可以将其他登录名添加到该同一角色。Each member of a fixed server role can add other logins to that same role. 用户定义的服务器角色的成员则无法将其他服务器主体添加到角色。Members of user-defined server roles cannot add other server principals to the role.

备注

服务器级权限不适用于 SQL 数据库或 SQL 数据仓库。Server-level permissions are not available in SQL Database or SQL Data Warehouse. 有关 SQL 数据库的详细信息,请参阅控制和授予数据库访问权限For more information about SQL Database, see Controlling and granting database access.

服务器级的固定角色Fixed Server-Level Roles

下表显示了服务器级的固定角色及其权限。The following table shows the fixed server-level roles and their capabilities.

服务器级的固定角色Fixed server-level role 描述Description
sysadminsysadmin sysadmin 固定服务器角色的成员可以在服务器上执行任何活动 。Members of the sysadmin fixed server role can perform any activity in the server.
serveradminserveradmin serveradmin 固定服务器角色的成员可以更改服务器范围的配置选项和关闭服务器。Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
securityadminsecurityadmin securityadmin 固定服务器角色的成员可以管理登录名及其属性。Members of the securityadmin fixed server role manage logins and their properties. 他们可以 GRANTDENYREVOKE 服务器级权限。They can GRANT, DENY, and REVOKE server-level permissions. 他们还可以 GRANTDENYREVOKE 数据库级权限(如果他们具有数据库的访问权限)。They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. 此外,他们还可以重置 SQL ServerSQL Server 登录名的密码。Additionally, they can reset passwords for SQL ServerSQL Server logins.

重要说明: 如果能够授予对 数据库引擎Database Engine 的访问权限和配置用户权限,安全管理员可以分配大多数服务器权限。IMPORTANT: The ability to grant access to the 数据库引擎Database Engine and to configure user permissions allows the security admin to assign most server permissions. securityadmin 角色应视为与 sysadmin 角色等效。The securityadmin role should be treated as equivalent to the sysadmin role.
processadminprocessadmin processadmin 固定服务器角色的成员可以终止在 SQL ServerSQL Server 实例中运行的进程 。Members of the processadmin fixed server role can end processes that are running in an instance of SQL ServerSQL Server.
setupadminsetupadmin setupadmin 固定服务器角色的成员可以使用 Transact-SQLTransact-SQL 语句添加和删除链接服务器 。Members of the setupadmin fixed server role can add and remove linked servers by using Transact-SQLTransact-SQL statements. (使用 Management StudioManagement Studio 时需要 sysadmin 成员资格 。)(sysadmin membership is needed when using Management StudioManagement Studio.)
bulkadminbulkadmin bulkadmin 固定服务器角色的成员可以运行 BULK INSERT 语句 。Members of the bulkadmin fixed server role can run the BULK INSERT statement.
diskadmindiskadmin diskadmin 固定服务器角色用于管理磁盘文件 。The diskadmin fixed server role is used for managing disk files.
dbcreatordbcreator dbcreator 固定服务器角色的成员可以创建、更改、删除和还原任何数据库。Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
publicpublic 每个 SQL ServerSQL Server 登录名都属于 public 服务器角色 。Every SQL ServerSQL Server login belongs to the public server role. 如果未向某个服务器主体授予或拒绝对某个安全对象的特定权限,该用户将继承授予该对象的 public 角色的权限。When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. 只有在希望所有用户都能使用对象时,才在对象上分配 Public 权限。Only assign public permissions on any object when you want the object to be available to all users. 你无法更改具有 Public 角色的成员身份。You cannot change membership in public.

注意: public 与其他角色的实现方式不同,可通过 public 固定服务器角色授予、拒绝或调用权限 。Note: public is implemented differently than other roles, and permissions can be granted, denied, or revoked from the public fixed server roles.

固定服务器角色的权限Permissions of Fixed Server Roles

每个固定服务器角色都被分配了特定的权限。Each fixed server role has certain permissions assigned to it. 下图显示了分配给服务器角色的权限。The following graphic shows the permissions assigned to the server roles.
fixed_server_role_permissions

重要

CONTROL SERVER 权限与 sysadmin 固定服务器角色类似,但并不完全相同。The CONTROL SERVER permission is similar but not identical to the sysadmin fixed server role. 权限并不表示角色成员身份,并且角色成员身份不会授予权限。Permissions do not imply role memberships and role memberships do not grant permissions. (例如,(E.g. CONTROL SERVER 不表示 sysadmin 固定服务器角色的成员身份。)但是,有时可在角色和相等的权限之间模拟。CONTROL SERVER does not imply membership in the sysadmin fixed server role.) However, it is sometimes possible to impersonate between roles and equivalent permissions. 大多数 DBCC 命令和许多系统过程要求 sysadmin 固定服务器角色的成员身份。Most DBCC commands and many system procedures require membership in the sysadmin fixed server role. 对于需要 sysadmin 成员资格的 171 个系统存储过程的列表,请参阅 Andreas Wolter 的以下博客帖子: CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats(CONTROL SERVER 与 sysadmin/sa:权限、系统过程、DBCC、自动创建架构和特权升级 - 注意事项)。For a list of 171 system stored procedures that require sysadmin membership, see the following blog post by Andreas Wolter CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation - caveats.

服务器级权限Server-Level Permissions

只能向用户定义的服务器角色中添加服务器级权限。Only server-level permissions can be added to user-defined server roles. 若要列出服务器级权限,请执行下面的语句。To list the server-level permissions, execute the following statement. 服务器级权限如下:The server-level permissions are:

SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;  

有关权限的详细信息,请参阅权限(数据库引擎)sys.fn_builtin_permissions (Tansact SQL)For more information about permissions, see Permissions (Database Engine) and sys.fn_builtin_permissions (Transact-SQL).

使用服务器级角色Working with Server-Level Roles

下表介绍了可以用于服务器级角色的命令、视图和功能。The following table explains the commands, views, and functions that you can use to work with server-level roles.

功能Feature 类型Type 描述Description
sp_helpsrvrole (Transact-SQL)sp_helpsrvrole (Transact-SQL) 元数据Metadata 返回服务器级角色的列表。Returns a list of server-level roles.
sp_helpsrvrolemember (Transact-SQL)sp_helpsrvrolemember (Transact-SQL) 元数据Metadata 返回有关服务器级角色成员的信息。Returns information about the members of a server-level role.
sp_srvrolepermission (Transact-SQL)sp_srvrolepermission (Transact-SQL) 元数据Metadata 显示服务器级角色的权限。Displays the permissions of a server-level role.
IS_SRVROLEMEMBER (Transact-SQL)IS_SRVROLEMEMBER (Transact-SQL) 元数据Metadata 指示 SQL ServerSQL Server 登录名是否为指定服务器级角色的成员。Indicates whether a SQL ServerSQL Server login is a member of the specified server-level role.
sys.server_role_members (Transact-SQL)sys.server_role_members (Transact-SQL) 元数据Metadata 为每个服务器级角色的每个成员返回一行。Returns one row for each member of each server-level role.
sp_addsrvrolemember (Transact-SQL)sp_addsrvrolemember (Transact-SQL) CommandCommand 将登录名添加为某个服务器级角色的成员。Adds a login as a member of a server-level role. 不推荐使用。Deprecated. 应改用 ALTER SERVER ROLEUse ALTER SERVER ROLE instead.
sp_dropsrvrolemember (Transact-SQL)sp_dropsrvrolemember (Transact-SQL) CommandCommand 从服务器级角色中删除 SQL ServerSQL Server 登录名或 Windows 用户或组。Removes a SQL ServerSQL Server login or a Windows user or group from a server-level role. 不推荐使用。Deprecated. 应改用 ALTER SERVER ROLEUse ALTER SERVER ROLE instead.
CREATE SERVER ROLE (Transact-SQL)CREATE SERVER ROLE (Transact-SQL) CommandCommand 创建用户定义的服务器角色。Creates a user-defined server role.
ALTER SERVER ROLE (Transact-SQL)ALTER SERVER ROLE (Transact-SQL) CommandCommand 更改服务器角色的成员关系或更改用户定义的服务器角色的名称。Changes the membership of a server role or changes name of a user-defined server role.
DROP SERVER ROLE (Transact-SQL)DROP SERVER ROLE (Transact-SQL) CommandCommand 删除用户定义的服务器角色。Removes a user-defined server role.
IS_SRVROLEMEMBER (Transact-SQL)IS_SRVROLEMEMBER (Transact-SQL) 函数Function 确定服务器角色的成员关系。Determines membership of server role.

另请参阅See Also

数据库级别的角色 Database-Level Roles
安全性目录视图 (Transact-SQL) Security Catalog Views (Transact-SQL)
安全函数 (Transact-SQL) Security Functions (Transact-SQL)
保护 SQL Server Securing SQL Server
授予服务器主体权限 (Transact-SQL) GRANT Server Principal Permissions (Transact-SQL)
撤消服务器主体权限 (Transact-SQL) REVOKE Server Principal Permissions (Transact-SQL)
拒绝服务器主体权限 (Transact-SQL) DENY Server Principal Permissions (Transact-SQL)
创建服务器角色Create a Server Role