ALTER SERVER ROLE (Transact-SQL)

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.

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).

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.

A. Changing the name of a server role

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

B. Adding a domain account to a server role

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

C. Adding a SQL Server login to a server role

ALTER SERVER ROLE diskadmin ADD MEMBER Ted ;
GO

D. Removing a domain account from a server role

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

E. Removing a SQL Server login from a server role

ALTER SERVER ROLE Production DROP MEMBER Ted ;
GO

F. Granting a login the permission to add logins to a user-defined server role

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

G. To view role membership

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

Reference

Concepts