Share via

ALTER SERVER ROLE (Transact-SQL)

  • Article

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

ALTER SERVER ROLE server_role_name 
{
    [ ADD MEMBER server_principal ]
  | [ DROP MEMBER server_principal ]
  | [ WITH NAME = new_server_role_name ]
} [ ; ]

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

C. Adding a SQL Server login to a server role

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

E. Removing a SQL Server login from a server role

The following example removes the SQL Server login Ted from the diskadmin fixed 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

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

See Also

Reference

CREATE SERVER ROLE (Transact-SQL)

DROP SERVER ROLE (Transact-SQL)

CREATE ROLE (Transact-SQL)

ALTER ROLE (Transact-SQL)

DROP ROLE (Transact-SQL)

Security Stored Procedures (Transact-SQL)

Security Functions (Transact-SQL)

sys.server_role_members (Transact-SQL)

sys.server_principals (Transact-SQL)

Concepts

Principals (Database Engine)