alter-role-transact-sql.md

---

title: "ALTER ROLE (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/13/2018" ms.prod: "sql" ms.prod_service: "database-engine, sql-database" ms.service: "" ms.component: "t-sql|statements" ms.reviewer: "" ms.suite: "sql" ms.technology:

• "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:
• "ALTER_ROLE_TSQL"
• "ALTER ROLE" dev_langs:
• "TSQL" helpviewer_keywords:
• "modifying database roles"
• "ALTER ROLE statement"
• "renaming database roles"
• "database roles [SQL Server], modifying"
• "names [SQL Server], database roles" ms.assetid: e1e83caa-17cc-4871-b2db-2711339fb64f caps.latest.revision: 64 author: "edmacauley" ms.author: "edmaca" manager: "craigg" ms.workload: "On Demand" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

---

ALTER ROLE (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-asdb-asdw-pdw-md]

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

Note

To alter roles adding or dropping members in [!INCLUDEssSDW] or [!INCLUDEssPDW], use sp_addrolemember (Transact-SQL) and sp_droprolemember (Transact-SQL).

Transact-SQL Syntax Conventions

Syntax

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

-- Syntax for SQL Server 2008, Azure SQL Data Warehouse and Parallel Data Warehouse
  
-- Change the name of a user-defined database role  
ALTER ROLE role_name   
    WITH NAME = new_name  
[;]  

Arguments

role_name
APPLIES TO: [!INCLUDEssNoVersion] (starting with 2008), [!INCLUDEssSDSfull]

Specifies the database role to change.

ADD MEMBER database_principall
APPLIES TO: [!INCLUDEssNoVersion] (starting with 2012), [!INCLUDEssSDSfull]

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: [!INCLUDEssNoVersion] (starting with 2012), [!INCLUDEssSDSfull]

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: [!INCLUDEssNoVersion] (starting with 2008), [!INCLUDEssSDSfull]

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.

Permissions

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

Limitations and restrictions

You cannot change the name of a fixed database role.

Metadata

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

• sys.database_role_members (Transact-SQL)

• sys.database_principals (Transact-SQL)

Examples

A. Change the name of a database role

APPLIES TO: [!INCLUDEssNoVersion] (starting with 2008), [!INCLUDEssSDS]

The following example changes the name of role buyers to purchasing. [!INCLUDEAdWorks-example]

ALTER ROLE buyers WITH NAME = purchasing;  

B. Add or remove role members

APPLIES TO: [!INCLUDEssNoVersion] (starting with 2012), [!INCLUDEssSDS]

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. [!INCLUDEAdWorks-example]

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

See Also

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)