alter-role-transact-sql.md

description	ALTER ROLE (Transact-SQL)
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.reviewer
ms.technology	t-sql
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
author	VanMSFT
ms.author	vanto
monikerRange	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

ALTER ROLE (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

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 Synapse Analytics and Parallel Data Warehouse
  
-- Change the name of a user-defined database role  
ALTER ROLE role_name   
    WITH NAME = new_name  
[;]  

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

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

Specifies the database role to change.

ADD MEMBER database_principal
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. This example can be executed in the AdventureWorks sample database.

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

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)