Granting privileges to the Workflow Manager (Classic) workspace components in SQL Server

Available with Workflow Manager license.

Complexity:

Beginner

Data Requirement:

ArcGIS Tutorial Data for Desktop

The geodatabase administrator must grant all users that will be accessing the Workflow Manager (Classic) workspace specific privileges to the tables. This can be accomplished by creating database roles and assigning the roles to individual users.

Copying and pasting the examples may cause syntax errors.

Granting permissions

In SQL Server Management Studio, grant permissions to the Workflow Manager (Classic) workspace tables through the schema. The sample script shows how to remove existing members from a role and drop the roles. Then re-create the role and assign permissions to the role through the schema.

Grant privileges:

USE [wmxdb]
GO
EXEC sp_droprolemember 'wmx_editor', 'giseditor'
GO
EXEC sp_droprole 'wmx_editor'
GO
EXEC sp_addrole 'wmx_editor', 'wmx'
GO
GRANT DELETE ON SCHEMA::[wmx] TO [wmx_editor]
GRANT EXECUTE ON SCHEMA::[wmx] TO [wmx_editor]
GRANT INSERT ON SCHEMA::[wmx] TO [wmx_editor]
GRANT SELECT ON SCHEMA::[wmx] TO [wmx_editor]
GRANT UPDATE ON SCHEMA::[wmx] TO [wmx_editor]
GO
EXEC sp_droprolemember 'wmx_viewer', 'gisviewer'
GO
EXEC sp_droprole 'wmx_viewer'
GO
EXEC sp_addrole 'wmx_viewer', 'wmx'
GO
GRANT SELECT ON SCHEMA::[wmx] TO [wmx_viewer]
GO

Verifying roles

This will list the roles.

Verify roles:

EXEC sp_helprolemember 'wmx_editor'
GO
EXEC sp_helprolemember 'wmx_viewer'
GO

Verifying role permission

This will list the privileges assigned to the role.

Verify role permissions:

select dp.NAME AS principal_name,
 dp.type_desc AS principal_type_desc,
 o.NAME AS object_name,
 p.permission_name,
 p.state_desc AS permission_state_desc 
 from sys.database_permissions p
 left OUTER JOIN sys.all_objects o
 on p.major_id = o.OBJECT_ID
 inner JOIN sys.database_principals dp
 on p.grantee_principal_id = dp.principal_id
 where dp.NAME in ('wmx_editor','wmx_viewer')
GO

Creating an editor user

Users should have their own login names. The example below shows how to create an editor geodatabase user.

Create the editor login:

USE master
GO
EXEC sp_addlogin N'giseditor', 'gis$editor', @logindb, @loginlang
GO

Create the user for the login in the WMXDB:

USE [wmxdb]
GO
CREATE USER [giseditor] FOR LOGIN [giseditor]
GO

Add the user to the editor role:

USE [wmxdb]
GO
EXEC sp_addrolemember N'wmx_editor', N'giseditor'
GO

Creating a viewer user

Users should have their own login names. The example below shows how to create a viewer geodatabase user.

Create the viewer login:

USE master
GO
EXEC sp_addlogin N'gisviewer', 'gis$viewer', @logindb, @loginlang
GO

Create the user for the login in the WMXDB:

USE [wmxdb]
GO
CREATE USER [gisviewer] FOR LOGIN [gisviewer]
GO

Add the user to the editor role:

USE [wmxdb]
GO
EXEC sp_addrolemember N'wmx_viewer', N'gisviewer'
GO