Skip to content

Latest commit

 

History

History
142 lines (86 loc) · 6.25 KB

join-a-role.md

File metadata and controls

142 lines (86 loc) · 6.25 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom f1_keywords helpviewer_keywords monikerRange
Join a Role
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.
VanMSFT
vanto
11/05/2024
sql
security
how-to
ignite-2024
SQL13.SWB.DATABASEUSER.MEMBERSHIP.F1
adding a member to a role
join a role
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

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