ALTER ROLE (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

ALTER ROLE (Transact-SQL)

 

Updated: January 19, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Adds or removes members to or from a database role, or changes the name of a user-defined database role.

System_CAPS_ICON_note.jpg Note


To alter roles in SQL Data Warehouse or Parallel Data Warehouse, use sp_addrolemember (Transact-SQL) and sp_droprolemember (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

-- SQL Server (starting with 2012) and SQL Database  
  
ALTER ROLE  role_name  
{  
       ADD MEMBER database_principal  
    |  DROP MEMBER database_principal  
    |  WITH NAME = new_name  
}  
[;]  

-- SQL Server 2008 only  
-- Change the name of a user-defined database role  
  
ALTER ROLE role_name   
    WITH NAME = new_name  
[;]  

role_name
APPLIES TO:  SQL Server (starting with 2008), Azure SQL Database

Specifies the database role to change.

ADD MEMBER database_principall
APPLIES TO:  SQL Server (starting with 2012), Azure SQL Database

Specifies to add the database principal to the membership of a database role.

  • database_principal is a database user or a user-defined database role.

  • database_principal cannot be a fixed database role or a server principal.

DROP MEMBER database_principal
APPLIES TO:  SQL Server (starting with 2012), Azure SQL Database

Specifies to remove a database principal from the membership of a database role.

  • database_principal is a database user or a user-defined database role.

  • database_principal cannot be a fixed database role or a server principal.

WITH NAME = new_name
APPLIES TO:  SQL Server (starting with 2008), Azure SQL Database

Specifies to change the name of a user-defined database role. The new name must not already exist in the database.

Changing the name of a database role does not change ID number, owner, or permissions of the role.

To run this command you need one or more of these permissions or memberships:

  • ALTER permission on the role

  • ALTER ANY ROLE permission on the database

  • Membership in the db_securityadmin fixed database role

Additionally, to change the membership in a fixed database role you need:

  • Membership in the db_owner fixed database role

You cannot change the name of a fixed database role.

These system views contain information about database roles and database principals.

A. Change the name of a database role

APPLIES TO:  SQL Server (starting with 2008), SQL Database

The following example changes the name of role buyers to purchasing. This example can be executed in the AdventureWorks sample database.

ALTER ROLE buyers WITH NAME = purchasing;  

B. Add or remove role members

APPLIES TO:  SQL Server (starting with 2012), SQL Database

This example creates a database role named Sales. It adds a database user named Barry to the membership, and then shows how to remove the member Barry. This example can be executed in the AdventureWorks sample database.

CREATE ROLE Sales;  
ALTER ROLE Sales ADD MEMBER Barry;  
ALTER ROLE Sales DROP MEMBER Barry;  

CREATE ROLE (Transact-SQL)
Principals (Database Engine)
DROP ROLE (Transact-SQL)
sp_addrolemember (Transact-SQL)
sys.database_role_members (Transact-SQL)
sys.database_principals (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft