Export (0) Print
Expand All
Expand Minimize
2 out of 4 rated this helpful - Rate this topic

ALTER SERVER ROLE (Transact-SQL)

SQL Server 2012

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

ALTER SERVER ROLE server_role_name 
{
    [ ADD MEMBER server_principal ]
  | [ DROP MEMBER server_principal ]
  | [ WITH NAME = new_server_role_name ]
} [ ; ]
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 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 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

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.