TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

ALTER SERVER ROLE (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012)noAzure SQL DatabasenoAzure SQL Data Warehouse 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.

Topic link icon Transact-SQL Syntax Conventions

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

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.

System_CAPS_ICON_note.jpg 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.

System_CAPS_ICON_note.jpg 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-worksroberto0] ;  

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

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  

H. Basic Syntax

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

ALTER SERVER ROLE LargeRC ADD MEMBER Anna;  

I. Remove a login from a resource class.

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

ALTER SERVER ROLE LargeRC DROP MEMBER Anna;  

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)
Principals (Database Engine)
sys.server_role_members (Transact-SQL)
sys.server_principals (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft