Creating Database Roles

See Also

To easily manage the permissions in your solution database, it is recommended you define a set of roles based on job functions and assign each role the permissions that apply to that job.

SQL Server roles exist within a database and cannot span more than one database. Because roles are unique to each database, you can reuse a role name, such as "Reviewer" in each database that you create.

To assign users and groups to database roles, the users and groups must have valid Windows NT domain accounts and SQL Server logins. For details, see Creating Windows NT User and Group Accounts, and Creating SQL Server Logins.

You have two options when creating database roles for your team solutions:

After creating your database roles, you can add users to your team solutions. For details, see Creating Team Solution Database Users.

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.

Creating SQL Server Database Roles with Enterprise Manager

By default, SQL Server Enterprise Manager is installed by SQL Server Setup as part of the server software on computers running Windows NT and as part of the client software on computers running Windows NT, Windows 95, and Windows 98.

To create a SQL Server 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 the databases group, and then expand a particular database.

  4. Right-click Roles, and then click New Database Role.

  5. Enter a Name.

  6. If you wish to assign users to this role, select Add.

  7. Click OK.

Repeat these steps as necessary to create your database roles.

Creating SQL Server Database Roles with Microsoft Access

Microsoft Access can be used to create database roles for your SQL Server database using an Access data project.

To create database roles using 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 Add.

  4. Enter a Name.

  5. If you wish to assign users to this role, select Add.

  6. Click OK.

Repeat these steps as necessary to create additional database roles.