Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
SQL database in Microsoft Fabric
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 Azure Synapse Analytics and Analytics Platform System (PDW) use sp_addrolemember (Transact-SQL) and sp_droprolemember (Transact-SQL).
Transact-SQL syntax conventions
Syntax for SQL Server (starting with 2012), Azure SQL Managed Instance, Azure SQL Database, and Microsoft Fabric.
ALTER ROLE role_name
{
ADD MEMBER database_principal
| DROP MEMBER database_principal
| WITH NAME = new_name
}
[;]
Syntax for SQL Server prior to 2012.
-- 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, Azure SQL Managed Instance
Specifies the database role to change.
ADD MEMBER database_principal
Applies to: SQL Server (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: SQL Server (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: SQL Server (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.
To run this command you need one or more of these permissions or memberships:
Additionally, to change the membership in a fixed database role you need:
You can't change the name of a fixed database role.
These system views contain information about database roles and database principals.
Applies to: SQL Server (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;
Applies to: SQL Server (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;
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;
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)
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today