Creating users and schemas for SQL Server databases

You must create the users and schemas after creating the SQL Server databases.

Before you begin

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.

Important: In Microsoft SQL server, the default schema name associated with a user must be the same as the user name. For example, if the user name for the Performance Data Warehouse database is dbuser then the default schema name associated with the user dbuser must also be named dbuser. You must create an ordinary database user and assign the required rights to the user instead of using a super user, such as sa. This is because the default schema for the super user is dbo and this cannot be changed.

You can complete the following steps if existing tables are not associated with a schema that is the same as the user name.

In SQL Server Management Studio Object Explorer, right-click the table name and then click Design.
From the Design view, press F4 to view the Properties window.
From the Properties window, update the schema name.
Right-click the tab and select Close to close the Design view.
Click OK when prompted to save. The selected table is transferred to the schema.
Repeat the previous steps for all the tables in the Performance Data Warehouse database.

About this task

The createUser.sql script is available in the BPM_HOME/BPM/dbscripts/SQLServer/Create folder. It is used to create the users and schema for the SQL Server databases.

Procedure

Locate the SQL scripts to run.
Run the scripts to create the users and schemas for SQL Server databases. For example, run the following sample script to create the required users.
```
BPM_HOME/BPM/dbscripts/SQLServer/Create/createUser.sql  
```
Optionally, if the above script is unavailable during configuration, an copy the contents of the above SQL file and run the commands from the command line as follows:
```
USE master
GO
CREATE LOGIN @DB_USER@ WITH PASSWORD='@DB_PASSWD@'
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@;
```
In the above example, replace @DB_NAME@ with the BPM database name for which you created users and schema, @DB_USER@ with the database user you want to create, and @DB_PASSWD@ with the password for that user.

What to do next

When you create database schemas the using the generated scripts, 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 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 Detailed SQL Server database privileges table at SQL Server database privileges.