ALTER SERVER ROLE (Transact-SQL)ALTER SERVER ROLE (Transact-SQL)

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

更改服务器角色的成员关系或更改用户定义的服务器角色的名称。Changes the membership of a server role or changes name of a user-defined server role. 无法重命名固定服务器角色。Fixed server roles cannot be renamed.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server  
  
ALTER SERVER ROLE server_role_name   
{  
    [ ADD MEMBER server_principal ]  
  | [ DROP MEMBER server_principal ]  
  | [ WITH NAME = new_server_role_name ]  
} [ ; ]  
-- Syntax for Parallel Data Warehouse  
  
ALTER SERVER ROLE  server_role_name  ADD MEMBER login;  
  
ALTER SERVER ROLE  server_role_name  DROP MEMBER login;  

参数Arguments

server_role_nameserver_role_name
要更改的服务器角色的名称。Is the name of the server role to be changed.

ADD MEMBER server_principalADD MEMBER server_principal
将指定的服务器主体添加到服务器角色中。Adds the specified server principal to the server role. server_principal 可以是登录名或用户定义的服务器角色。server_principal can be a login or a user-defined server role. server_principal 不能是固定服务器角色、数据库角色或 sa。server_principal cannot be a fixed server role, a database role, or sa.

DROP MEMBER server_principalDROP MEMBER server_principal
从服务器角色中删除指定的服务器主体。Removes the specified server principal from the server role. server_principal 可以是登录名或用户定义的服务器角色。server_principal can be a login or a user-defined server role. server_principal 不能是固定服务器角色、数据库角色或 sa。server_principal cannot be a fixed server role, a database role, or sa.

WITH NAME =new_server_role_nameWITH NAME =new_server_role_name
指定用户定义的服务器角色的新名称。Specifies the new name of the user-defined server role. 服务器中不能已存在此名称。This name cannot already exist in the server.

RemarksRemarks

更改用户定义的服务器角色名称并不会更改角色的 ID 号、所有者或权限。Changing the name of a user-defined server role does not change ID number, owner, or permissions of the role.

为了更改角色成员身份,ALTER SERVER ROLE 会替换 sp_addsrvrolemember 和 sp_dropsrvrolemember。For changing role membership, ALTER SERVER ROLE replaces sp_addsrvrolemember and sp_dropsrvrolemember. 不推荐使用这些存储过程。These stored procedures are deprecated.

您可以通过查询 sys.server_role_memberssys.server_principals 目录视图来查看服务器角色。You can view server roles by querying the sys.server_role_members and sys.server_principals catalog views.

若要更改用户定义的服务器角色的所有者,请使用 ALTER AUTHORIZATION (Transact-SQL)To change the owner of a user-defined server role, use ALTER AUTHORIZATION (Transact-SQL).

权限Permissions

要求在服务器上具有 ALTER ANY SERVER ROLE 权限才能更改用户定义的服务器角色的名称。Requires ALTER ANY SERVER ROLE permission on the server to change the name of a user-defined server role.

固定服务器角色Fixed server roles

若要为固定服务器角色添加成员,您必须是该固定服务器角色的成员,或者是 sysadmin 固定服务器角色的成员。To add a member to a fixed server role, you must be a member of that fixed server role, or be a member of the sysadmin fixed server role.

备注

CONTROL SERVERALTER ANY SERVER ROLE 权限不足以为固定服务器角色执行 ALTER SERVER ROLE,并且不能为固定服务器角色授予 ALTER权限。The CONTROL SERVER and ALTER ANY SERVER ROLE permissions are not sufficient to execute ALTER SERVER ROLE for a fixed server role, and ALTER permission cannot be granted on a fixed server role.

用户定义的服务器角色User-defined server roles

若要为用户定义的服务器角色添加成员,您必须是 sysadmin 固定服务器角色的成员,或者具有 CONTROL SERVERALTER ANY SERVER ROLE 权限。To add a member to a user-defined server role, you must be a member of the sysadmin fixed server role or have CONTROL SERVER or ALTER ANY SERVER ROLE permission. 否则,必须具有该角色的 ALTER 权限。Or you must have ALTER permission on that role.

备注

与固定服务器角色不同,用户定义的服务器角色的成员本身并不具备为该同一角色添加成员的权限。Unlike fixed server roles, members of a user-defined server role do not inherently have permission to add members to that same role.

示例Examples

A.A. 更改服务器角色的名称Changing the name of a server role

以下示例创建一个名为 Product 的服务器角色,然后将该服务器角色的名称更改为 ProductionThe following example creates a server role named Product, and then changes the name of server role to Production.

CREATE SERVER ROLE Product ;  
ALTER SERVER ROLE Product WITH NAME = Production ;  
GO  

B.B. 在服务器角色中添加域帐户Adding a domain account to a server role

以下示例在名为 Production 的用户定义服务器角色中添加一个名为 adventure-works\roberto0 的域帐户。The following example adds a domain account named adventure-works\roberto0 to the user-defined server role named Production.

ALTER SERVER ROLE Production ADD MEMBER [adventure-works\roberto0] ;  

C.C. 在服务器角色中添加 SQL Server 登录名Adding a SQL Server login to a server role

以下示例在 diskadmin 固定服务器角色中添加一个名为 TedSQL ServerSQL Server 登录名。The following example adds a SQL ServerSQL Server login named Ted to the diskadmin fixed server role.

ALTER SERVER ROLE diskadmin ADD MEMBER Ted ;  
GO  

D.D. 从服务器角色中删除域帐户Removing a domain account from a server role

以下示例从名为 Production 的用户定义服务器角色中删除一个名为 adventure-works\roberto0 的域帐户。The following example removes a domain account named adventure-works\roberto0 from the user-defined server role named Production.

ALTER SERVER ROLE Production DROP MEMBER [adventure-works\roberto0] ;  

E.E. 从服务器角色中删除 SQL Server 登录名Removing a SQL Server login from a server role

以下示例从 diskadmin 固定服务器角色中删除 SQL ServerSQL Server 登录名 TedThe following example removes the SQL ServerSQL Server login Ted from the diskadmin fixed server role.

ALTER SERVER ROLE Production DROP MEMBER Ted ;  
GO  

F.F. 为登录名授予权限以将登录名添加到用户定义的服务器角色中Granting a login the permission to add logins to a user-defined server role

以下示例允许 Ted 将其他登录名添加到名为 Production 的用户定义服务器角色中。The following example allows Ted to add other logins to the user-defined server role named Production.

GRANT ALTER ON SERVER ROLE::Production TO Ted ;  
GO  

G.G. 查看角色成员身份To view role membership

若要查看角色成员身份,请使用 SQL Server Management StudioSQL Server Management Studio 中的服务器角色(成员)页或执行以下查询:To view role membership, use the Server Role (Members) page in SQL Server Management StudioSQL Server Management Studio or execute the following query:

SELECT SRM.role_principal_id, SP.name AS Role_Name,   
SRM.member_principal_id, SP2.name  AS Member_Name  
FROM sys.server_role_members AS SRM  
JOIN sys.server_principals AS SP  
    ON SRM.Role_principal_id = SP.principal_id  
JOIN sys.server_principals AS SP2   
    ON SRM.member_principal_id = SP2.principal_id  
ORDER BY  SP.name,  SP2.name  

示例:并行数据仓库Parallel Data WarehouseExamples: 并行数据仓库Parallel Data Warehouse

H.H. 基本语法Basic Syntax

以下示例将登录名 Anna 添加到 LargeRC 服务器角色中。The following example adds the login Anna to the LargeRC server role.

ALTER SERVER ROLE LargeRC ADD MEMBER Anna;  

I.I. 从资源类中删除登录。Remove a login from a resource class.

以下示例在 LargeRC 服务器角色中删除 Anna 成员身份。The following example drops Anna's membership in the LargeRC server role.

ALTER SERVER ROLE LargeRC DROP MEMBER Anna;  

另请参阅See Also

CREATE SERVER ROLE (Transact-SQL) CREATE SERVER ROLE (Transact-SQL)
DROP SERVER ROLE (Transact-SQL) DROP SERVER ROLE (Transact-SQL)
CREATE ROLE (Transact-SQL) CREATE ROLE (Transact-SQL)
ALTER ROLE (Transact-SQL) ALTER ROLE (Transact-SQL)
DROP ROLE (Transact-SQL) DROP ROLE (Transact-SQL)
安全存储过程 (Transact-SQL) Security Stored Procedures (Transact-SQL)
安全函数 (Transact-SQL) Security Functions (Transact-SQL)
主体(数据库引擎) Principals (Database Engine)
sys.server_role_members (Transact-SQL) sys.server_role_members (Transact-SQL)
sys.server_principals (Transact-SQL)sys.server_principals (Transact-SQL)