Assigning Users to Database Roles

See Also

Roles can simplify security administration in databases with a large number of users or with a complex security system. SQL Server logins, Windows NT users and groups, and other SQL Server database roles all can be added as members of a role in your team solution. Because a role is restricted to a single team solution, you can add users, groups, and roles known only to that solution.

When you add a new user account to your database or you must change the permissions of an existing user, you can add the user to a team solution database role rather than applying permissions directly to the account. This is particularly advantageous when you plan to create a template based on your solution. In a template, roles with the appropriate permissions are stored with the template. You can choose whether to include the specific users in the template.

While you cannot add users, groups, or roles from one team solution to a role in another team solution, you can share users across different team solutions by using Windows NT groups. Instead of adding individual users to your team solution, you can add Windows NT group domain accounts.

Note   A database role is owned by either the user explicitly specified as the owner when the role is created or the user who created the role when no owner is specified. The owner of the role determines who can be added or removed from the role. However, because a role is not a database object, multiple roles of the same name in the same database owned by different users cannot be created.

Note   If you make any changes to the membership of database roles in your team solution, you need to synchronize the user directory in order for role permissions to work properly. For details, see Synchronizing User Information in the Access Workflow Designer Administrator's Guide.

To add a user to a database role using the Enterprise Manager

1. From the Start menu, select Programs, then Microsoft SQL Server 7.0, and then Enterprise Manager.
   
   
2. Expand a server group, and then expand a server.
   
   
3. Expand Databases, and then expand the database in which the role exists.
   
   
4. Click Roles.
   
   
5. In the details pane, right-click the role to which you want to add the user, and then select Properties.
   
   
6. Click Add. Then, click a user or users to add. Only users in the current database can be added to the role. If you must add a database user, see Creating Team Solution Database Users.

   Note   When you add a Windows NT login that does not have a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically.

7. Click OK.
   
   
8. Click OK.

Repeat these steps as necessary to add users to other database roles.

To add a user to a database role using Microsoft Access

1. Open your SQL Server database in Access using an Access data project. For details, see Opening your Solution in the Access Workflow Designer in the Access Workflow Designer Developer's Guide.
   
   
2. From the Tools menu, select Security, and then Database Security.

   Note   When an Access data project is loaded and you are working in the Access Workflow Designer, you can access the Security option from the Tools menu in the designer as well.

3. On the Database Roles tab, select a role from the list, and then select Edit.
   
   
4. Click Add. Then, click a user or users to add. Only users in the current database can be added to the role. If you must add a database user, see Creating Team Solution Database Users.
   
   
5. Click OK.
   
   
6. Click OK.

Repeat these steps as necessary to add users to other database roles.