Muokkaa

Jaa

Join a Role

  • Artikkeli

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric

This article describes how to assign roles to logins and database users in SQL Server by using SQL Server Management Studio or Transact-SQL. Use roles in SQL Server 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.

SQL Server 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 SQL Server. 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 SQL Server.

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 Database Engine.

  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 Database Engine.

  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 SQL database in Microsoft Fabric, 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 SQL database in Microsoft Fabric, only database-level users and roles are supported. In SQL database in Microsoft Fabric, 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