ALTER SERVER ROLE (Transact-SQL)

**APPLIES TO:** ![yes](media/yes.png)SQL Server (starting with 2012) ![no](media/no.png)Azure SQL Database ![no](media/no.png)Azure SQL Data Warehouse ![yes](media/yes.png)Parallel Data Warehouse

Changes the membership of a server role or changes name of a user-defined server role. Fixed server roles cannot be renamed.

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_name
Is the name of the server role to be changed.

ADD MEMBER server_principal
Adds the specified server principal to the server role. server_principal can be a login or a user-defined server role. server_principal cannot be a fixed server role, a database role, or sa.

DROP MEMBER server_principal
Removes the specified server principal from the server role. server_principal can be a login or a user-defined server role. server_principal cannot be a fixed server role, a database role, or sa.

WITH NAME =new_server_role_name
Specifies the new name of the user-defined server role. This name cannot already exist in the server.

Remarks

Changing the name of a user-defined server role does not change ID number, owner, or permissions of the role.

For changing role membership, ALTER SERVER ROLE replaces sp_addsrvrolemember and sp_dropsrvrolemember. These stored procedures are deprecated.

You can view server roles by querying the sys.server_role_members and sys.server_principals catalog views.

To change the owner of a user-defined server role, use ALTER AUTHORIZATION (Transact-SQL).

Permissions

Requires ALTER ANY SERVER ROLE permission on the server to change the name of a user-defined server role.

Fixed server roles

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.

[!NOTE]
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

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. Or you must have ALTER permission on that role.

[!NOTE]
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. Changing the name of a server role

The 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. Adding a domain account to a server role

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] ;

The following example adds a SQL Server login named Ted to the diskadmin fixed server role.

ALTER SERVER ROLE diskadmin ADD MEMBER Ted ;  
GO

D. Removing a domain account from a server role

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] ;

The following example removes the SQL Server login Ted from the diskadmin fixed server role.

ALTER SERVER ROLE Production DROP MEMBER Ted ;  
GO

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. To view role membership

To view role membership, use the Server Role (Members) page in SQL 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

Examples: [!INCLUDEssPDW]

H. Basic Syntax

The following example adds the login Anna to the LargeRC server role.

ALTER SERVER ROLE LargeRC ADD MEMBER Anna;

The following example drops Anna’s membership in the LargeRC server role.

ALTER SERVER ROLE LargeRC DROP MEMBER Anna;