Skip to content

alter-role-transact-sql.md

Latest commit

 

History

History
146 lines (106 loc) · 6.36 KB

alter-role-transact-sql.md

File metadata and controls

146 lines (106 loc) · 6.36 KB

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.suite: "sql" ms.technology: t-sql 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: CarlRabeler ms.author: carlrab manager: craigg monikerRange: ">=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017"

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

Topic link icon 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.

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)