Share via


Security Categories in Workflow Designer for SQL Server

There are three security categories for Workflow Designer for SQL Server users:

  • Server administrator

  • Workflow application owner and workflow application developer

    Because of the complementary duties of owners and developers, the positions are generally combined into a single category. Typically, the category is referred to as owner, because, while it is possible to be an owner without being a developer, to be a developer you also must be an owner.

  • Workflow application user

Each of these categories is associated with specific responsibilities and permissions in Microsoft® Windows® 2000, Microsoft® SQL Server™, Microsoft® FrontPage®, and the workflow application that makes it possible for them to carry out those responsibilities. In some cases, the duties of the server administrator and the workflow application owner might overlap.

Server Administrator

Typically, the server administrator is responsible for the following activities:

  • Creating Windows groups and users
  • Creating SQL Server user logins and roles
  • Assigning Windows groups and users to SQL Server roles
  • Optionally assigning SQL Server database owner permissions (db_owner) to developers to make it possible for workflow application development to be done on existing SQL Server databases
  • Setting up and executing workflow application User Directory synchronization
  • Editing user information

Workflow Application Owner

The workflow application owner must be a member of the modAppOwners group. Generally, this role is responsible for the following activities:

  • Creating SQL Server roles
  • Assigning existing users to SQL Server roles
  • Executing workflow application User Directory synchronization
  • Defining role permissions for workflow events, database roles, and row-level security

Workflow Application Developer

The workflow application developer must be a member of the modAppOwners group. Usually, this role is responsible for the following activities:

  • Creating and registering new workflow applications
  • Creating new database templates
  • Importing and exporting templates

Workflow Application User

A user who accesses the workflow application through a client browser has few responsibilities requiring special permissions. Instead, the workflow application user is typically assigned to a Windows group domain account associated with a database role. The user's privileges when using the application depend on the role to which the user belongs.

Note   It is recommended you use group accounts to simplify server administration.

Permissions by Security Category

The permissions associated with these security categories are as follows:

Security Category Windows 2000 account SQL Server role FrontPage permissions
Server
administrator
System
administrator
Sysadmin

dbo in modSystem database

System administrator
Workflow application
owner
modAppOwners
group
modAppOwners database role

dbo for the workflow applications they create

db_owner permissions (required to register existing SQL Server databases as workflow applications)

FrontPage administrator group
Workflow application
user
User or group
account
SQL Server user or group login associated with Windows 2000 accounts none

When Workflow Services for SQL Server are installed, setup creates a group called modAppOwners and sets all required Distributed Component Object Model (DCOM) permissions that make it possible for members of modAppOwners to create workflow applications.

Setup also creates a SQL Server login for the Windows modAppOwners group called <servername>\modAppOwners. This login is added to the database creators (db_creator) role on the server and db_user on the master and modSystem databases. These permissions make it possible for application owners to create workflow applications based on templates, create new SQL Server databases, and enable workflow on existing databases.

In addition, the modAppOwners Windows group is added to the FrontPage Administrator group, so members can create Web sites for workflow applications.

Permissions Defined by the Server Administrator

To grant a developer the appropriate permissions, the server administrator must add the developer to the Windows modAppOwners group using the Windows 2000 Server User Manager.

If the developer wants to enable workflow on an existing database, then the administrator must make the developer a database owner (db_owner) on this database. The person who creates a database is automatically the db_owner.

In addition, if the members of the modAppOwners group typically will be performing administrative tasks, such as creating new SQL Server logins, the server administrator should grant the modAppOwners group system administrator privileges on the SQL Server. This is not the default.

Permissions Defined by the Workflow Application Owner

As a member of the modAppOwners group, the workflow application owner can create SQL Server roles appropriate for a workflow application, add project users or groups to these roles, and set permissions on these roles as required by the workflow application. To create the roles, SQL Server logins and Windows 2000 accounts must exist already.

When developing the project, modAppOwners members can set permissions for workflow events within the Workflow Designer for SQL Server.

Row-level permissions can be set using stored procedures: modGrantRowPermissions, modDropRowPermissions, and modEnumRowPermissions. See the Issue Tracking sample for an example of how to handle row-level permissions using these stored procedures.

Permissions Defined by Workflow Application Users

If row-level permissions are enabled in the workflow application, users can specify permissions for individual database records based on project roles. If a record is assigned to a user, that user can select specific roles to have read-only or write permissions for the issue.

See Also

Setting Up Accounts, Logins, Roles, and Users | Windows 2000 and SQL Server Security | Creating Database Roles | Creating Workflow Application Users | Assigning Users to Database Roles | Defining Permissions for Database Roles