Skip to content

Latest commit

 

History

History
153 lines (109 loc) · 7.18 KB

alter-role-transact-sql.md

File metadata and controls

153 lines (109 loc) · 7.18 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
ALTER ROLE (Transact-SQL)
ALTER ROLE (Transact-SQL)
VanMSFT
vanto
09/08/2022
sql
t-sql
reference
ALTER_ROLE_TSQL
ALTER ROLE
modifying database roles
ALTER ROLE statement
renaming database roles
database roles [SQL Server], modifying
names [SQL Server], database roles
TSQL
>=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

ALTER ROLE (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-fabricse-fabricdw]

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

Note

To add or drop members from roles in [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW] use sp_addrolemember (Transact-SQL) and sp_droprolemember (Transact-SQL).

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

Syntax for [!INCLUDEssNoVersion] (starting with 2012), [!INCLUDEssazuremi-md], [!INCLUDE ssazure-sqldb], and [!INCLUDE fabric].

ALTER ROLE  role_name  
{  
       ADD MEMBER database_principal  
    |  DROP MEMBER database_principal  
    |  WITH NAME = new_name  
}  
[;]

Syntax for [!INCLUDEssNoVersion] prior to 2012.

-- 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), Azure SQL Database, Azure SQL Managed Instance

Specifies the database role to change.

ADD MEMBER database_principal
Applies to: [!INCLUDEssNoVersion] (starting with 2012), Azure SQL Database, Azure SQL Managed Instance

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 can't be a fixed database role or a server principal.

DROP MEMBER database_principal
Applies to: [!INCLUDEssNoVersion] (starting with 2012), Azure SQL Database, Azure SQL Managed Instance

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 can't be a fixed database role or a server principal.

WITH NAME = new_name
Applies to: [!INCLUDEssNoVersion] (starting with 2008), Azure SQL Database, Azure SQL Managed Instance

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 doesn't 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 can't 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), Azure SQL Database, Azure SQL Managed Instance

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), Azure SQL Database, Azure SQL Managed Instance

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;

C. Add a role member to special roles for Azure SQL Database

Applies to: Azure SQL Database

This example creates a SQL login in the virtual master database, creates a database user that's related to that server login, and adds the database user as a member of the special role dbmanager. The example allows the user permissions to create and drop databases on an Azure SQL Database logical server. Run the example in the virtual master database of the Azure SQL Database logical server.

 CREATE LOGIN sqllogin_nlastname WITH password='aah3%#om1os';
    
 CREATE USER sqllogin_nlastname FOR LOGIN sqllogin_nlastname 
 WITH DEFAULT_SCHEMA = master;
    
 ALTER ROLE [dbmanager] add member sqllogin_nlastname;

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)