Join a Role
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This topic 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.
In This Topic
Before you begin:
To assign roles to logins and database users, using:
Before You Begin
Limitations and Restrictions
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.
Security
Permissions
Requires ALTER ANY ROLE permission on the database, ALTER permission on the role, or membership in db_securityadmin.
Using SQL Server Management Studio
Note
The two procedures in this section only apply to SQL Server.
To add a member to a fixed server role
In Object Explorer, expand the server in which you want to edit a fixed server role.
Expand the Security folder.
Expand the Server Roles folder
Right-click the role you want to edit and select Properties.
In the Server Role Properties -server_role_name dialog box, on the Members page, click Add.
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, click Browse... and select any or all of the available objects in the Browse for Objects dialog box. Click OK to return to the Server Role Properties -server_role_name dialog box.
Select OK.
To add a member to a user-defined database role
In Object Explorer, expand the server in which you want to edit a user-defined database role.
Expand the Databases folder.
Expand the database in which you want to edit a user-defined database role.
Expand the Security folder.
Expand the Roles folder.
Expand the Database Roles folder.
Right-click the role you want to edit and select Properties.
In the Database Role Properties -database_role_name dialog box, in the General page, click Add.
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, click Browse... and select any or all of the available objects in the Browse for Objects dialog box. Click OK to return to the Database Role Properties -database_role_name dialog box.
Select OK.
Using Transact-SQL
To add a member to a fixed server role
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
ALTER SERVER ROLE diskadmin ADD MEMBER [Domain\Juan] ; GO
For more information, see ALTER SERVER ROLE (Transact-SQL).
To add a member to a user-defined database role
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
ALTER ROLE Marketing ADD MEMBER [Domain\Juan] ; GO
For more information, see ALTER ROLE (Transact-SQL).