Skip to content

join-a-role.md

Latest commit

 

History

History
142 lines (86 loc) · 6.26 KB

join-a-role.md

File metadata and controls

142 lines (86 loc) · 6.26 KB
title Join a Role
description Learn how to assign roles to logins and database users in SQL Server by using SQL Server Management Studio or Transact-SQL. Use roles to manage permissions.
author VanMSFT
ms.author vanto
ms.date 11/05/2024
ms.service sql
ms.subservice security
ms.topic how-to
ms.custom
ignite-2025
f1_keywords
SQL13.SWB.DATABASEUSER.MEMBERSHIP.F1
helpviewer_keywords
adding a member to a role
join a role
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

Join a Role

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics PDW FabricSQLDB]

This article describes how to assign roles to logins and database users in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. Use roles in [!INCLUDEssNoVersion] to efficiently manage permissions. Assign permissions to roles, and then add and remove users and logins to the roles. By using roles, permissions do not have to be individually maintained for each user.

[!INCLUDEssNoVersion] supports four types of roles.

  • Fixed server roles

  • User-defined server roles

  • Fixed database roles

  • User-defined database roles

The fixed roles are automatically available in [!INCLUDEssNoVersion]. Fixed roles have the necessary permissions to accomplish common tasks. For more information about fixed roles, see the following links. User-defined roles are created by you, and can be customized with the permissions that you select. For more information about user-defined roles, see the following links.

Use SQL Server Management Studio

Note

The two procedures in this section only apply to [!INCLUDEssnoversion].

Add a member to a fixed server role

  1. In Object Explorer, expand the server in which you want to edit a fixed server role.

  2. Expand the Security folder.

  3. Expand the Server Roles folder.

  4. Right-click the role you want to edit and select Properties.

  5. In the Server Role Properties dialog box, select the Members page, select Add.

  6. In the Select Server Login or Role dialog box, under Enter the object names to select (examples), enter the login or server role to add to this server role. Alternately, select Browse... and select any or all of the available objects in the Browse for Objects dialog box. Select OK to return to the Server Role Properties dialog box.

  7. Select OK.

Add a member to a user-defined database role

  1. In Object Explorer, expand the server in which you want to edit a user-defined database role.

  2. Expand the Databases folder.

  3. Expand the database in which you want to edit a user-defined database role.

  4. Expand the Security folder.

  5. Expand the Roles folder.

  6. Expand the Database Roles folder.

  7. Right-click the role you want to edit and select Properties.

  8. In the Database Role Properties -database_role_name dialog box, in the General page, select Add.

  9. In the Select Database User or Role dialog box, under Enter the object names to select (examples), enter the login or database role to add to this database role. Alternately, select Browse... and select any or all of the available objects in the Browse for Objects dialog box. Select OK to return to the Database Role Properties -database_role_name dialog box.

  10. Select OK.

Use Transact-SQL

Add a member to a fixed server role

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    ALTER SERVER ROLE diskadmin ADD MEMBER [Domain\Juan] ;  
GO

For more information, see ALTER SERVER ROLE (Transact-SQL).

Add a member to a user-defined database role

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute.

    ALTER ROLE Marketing ADD MEMBER [Domain\Juan] ;  
GO

For more information, see ALTER ROLE (Transact-SQL).

Permissions

Requires ALTER ANY ROLE permission on the database, ALTER permission on the role, or membership in db_securityadmin.

In [!INCLUDE fabric-sqldb], users/apps with the Write item permission in Fabric can grant any permissions.

Limitations

  • Changing the name of a database role does not change ID number, owner, or permissions of the role.
  • Database roles are visible in the sys.database_role_members and sys.database_principals catalog views.
  • In [!INCLUDE fabric-sqldb], only database-level users and roles are supported. In [!INCLUDE fabric-sqldb], Microsoft Entra ID for database users is the only supported authentication method. For more information, see Authorization in SQL database in Microsoft Fabric.

Related content