Security Categories in Workflow Designer for SQL Server

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

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

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.