SQL Server database privileges

Set database privileges to determine the authority that you must have to create or access your data store tables for SQL Server databases.

Permissions in SQL Server are assigned to roles which can be assigned to users, similar to windows user groups. There are two types of roles:

Server roles provision database server related permissions such as backup, shutdown, creating new databases, managing logins, and linking to other servers.
Database roles provision more traditional database permissions such as table access and those listed below.

Note: You can be a member of multiple roles such that you can combine the permissions of different fixed roles to find just the right combination for your requirements.

Assign the IBM® Business Process Manager database user to the following three roles:

Note: The database must be created by the database administrator who can then assign these roles to the database user for IBM Business Process Manager.

db_ddladmin
db_datawriter
db_datareader

For information about the permissions that are provided by these roles, see documentation from Microsoft.

When you create database schemas using the typical installation or database scripts that are generated using the BPMConfig command-line utility, your user ID must have the authority to create tables. When the tables are created, you must have the authority to select, insert, update, and delete information in the tables.

The above three fixed database roles cover 80% of the requirements, the remaining permissions are:

•CREATE INDEXTYPE: No INDEXTYPE. but db_ddladmin can create indexes and specify the type
•CREATE TABLESPACE: No TABLESPACE but similar concepts are WORKLOAD GROUP, RESOURCE POOL, and PARTITION SCHEME
•ALTER LOCK TABLE: This functionality is available through Lock hinting using the read or write commands (eg SELECT * FROM tbl name WHERE TABLOCKX
•CREATE SEQUENCE: This command is in the SQL Server 2012 documentation but not 2008 R2

The following table describes the database privileges that are needed to access the data stores.

Table 1. Database privileges
Minimum privileges that are required to create objects in the database	Minimum privileges that are required to access objects in the database
The user ID ideally requires DB OWNER privileges on the data stores used for IBM Business Process Manager.	Configure the SQL Server for SQL Server and Windows authentication so that authentication to be based on an SQL server login ID and password. The user ID must be the owner of the tables, or a member of a group that has sufficient authority to issue TRUNCATE TABLE statements. See the following table for the detailed privileges.

The following table describes more SQL Server database privileges for IBM Business Process Manager components. The installation privileges are the privileges that are required to install and configure the product. The runtime privileges are the database privileges that are required to run the product.

Table 2. Detailed SQL Server database privileges
Component	Installation privileges	Runtime privileges
Common DB	CREATE TABLE, ALTER TABLE, INSERT, CREATE USER, ALTER USER	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE The runtime user must have USAGE ON SEQUENCE privileges on all sequences in the createSchema_*.sql script for the common DB.
Business Space	CREATE TABLE, ALTER TABLE, INSERT, CREATE USER, ALTER USER	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE
Business Process Choreographer	CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE USER, CREATE PROCEDURE	SELECT, UPDATE, DELETE, INSERT
Messaging Engines	CREATE TABLE	SELECT, UPDATE, DELETE, INSERT, DROP ANY TABLE Note: Messaging Engines use the TRUNCATE TABLE SQL statement, which might require the DROP ANY TABLE privilege. See Database privileges.
Process Server	CREATE TABLE, SELECT, INSERT, UPDATE, DELETE TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, DROP VIEW, CREATE PROCEDURE, CREATE USER, ALTER USER	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, DROP TABLE, DROP VIEW
Performance Data Warehouse	CREATE TABLE, SELECT, INSERT, UPDATE, DELETE TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW, DROP VIEW, CREATE PROCEDURE, CREATE USER, ALTER USER	SELECT, UPDATE, DELETE, INSERT, CREATE VIEW, CREATE PROCEDURE, DROP TABLE, DROP VIEW
IBM BPM document store		db_datawriter db_datareader db_ddladmin public When you configure your database for the IBM BPM document store, a database capability that is named EmbeddedECM is used. The privileges listed for the IBM BPM content store are required for the database in the property file containing the EmbeddedECM capability.

Syntax for creating BPMDB and PDWDB databases in SQL Server is CREATE DATABASE @DB_NAME@ COLLATE SQL_Latin1_General_CP1_CI_AS;

Syntax for creating the CommonDB database in SQL Server is CREATE DATABASE @DB_NAME@ COLLATE SQL_Latin1_General_CP1_CS_AS;

Note:

The BPMDB and PDWDB databases must be created as case-insensitive. Use the command COLLATE SQL_Latin1_General_CP1_CI_AS, where CI is the COLLATE attribute value that is applicable for the case-insensitive databases.
The CommonDB database must be created as case-sensitive. Use the command COLLATE SQL_Latin1_General_CP1_CS_AS, where CS is the COLLATE attribute value that is applicable for the case-sensitive databases.

Syntax for creating the users and schemas for the SQL Server databases is as follows:

USE MASTER
GO
CREATE LOGIN @DB_USER@ WITH PASSWORD='@DB_PASSWD@'
GO
CREATE USER @DB_USER@ FOR LOGIN @DB_USER@ WITH DEFAULT_SCHEMA=@DB_USER@ 
GO
EXEC sp_addrolemember N'SqlJDBCXAUser', N'@DB_USER@';
GO

USE @DB_NAME@
GO
CREATE USER @DB_USER@ FOR LOGIN @DB_USER@ WITH DEFAULT_SCHEMA=@DB_USER@
GO
CREATE SCHEMA @DB_USER@ AUTHORIZATION @DB_USER@
GO 
EXEC sp_addrolemember 'db_ddladmin', @DB_USER@;
EXEC sp_addrolemember 'db_datareader', @DB_USER@;
EXEC sp_addrolemember 'db_datawriter', @DB_USER@;

Important: For all database users, note the following restrictions:

The user cannot be assigned to the system administrator (SYSADMIN) role.
The user must be mapped to the master database with the SqlJDBCXA user role.

For more information, see the WebSphere® Application Server page in the related reference.