Join a Role

This topic describes how to assign roles to logins and database users in SQL Server 2012 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:

  Limitations and Restrictions

  Security

• To assign roles to logins and database users, using:

  SQL Server Management Studio

  Transact-SQL

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.

[Top]

Security

Permissions

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

[Top]

Using SQL Server Management Studio

To 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 – server_role_name dialog box, on the Members page, click 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, 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.

7. Click OK.

To 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 Server 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, click 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, 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.

10. Click OK.

[Top]

Using Transact-SQL

To add a member to a fixed server role

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

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

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

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

To 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, click New Query.

3. 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 sp_addrolemember (Transact-SQL).

[Top]

See Also

Reference

Server-Level Roles

Database-Level Roles

Concepts

Application Roles