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

When using Windows authentication, you must ensure that the domain account is added to the SQL Sever login. You must login as the SQL Server administrator and follow the below steps to create users and schemas for Windows authentication.

Procedure

Create the SQL Server login account for your Windows machine using the login information for the machine. For example, if user1 is the user name and password is password for your Windows machine, use the following command syntax:
```
USE [master]
GO
CREATE LOGIN [user1\password] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
```
Note: The SQL Server login that is created must not have SYSADMIN privileges. If the login user has SYSADMIN privileges, the specified schema value is ignored for database connections by SQL Server since the sysadmin user's default schema is always dbo.

Update the master database to grant permission for XA transactions for the Windows user. Use the following command syntax:

USE [master];
CREATE USER [sqluser] FOR LOGIN [user1\password];
USE [master];
EXEC sp_addrolemember N'SqlJDBCXAUser', N'sqluser';

For each database that you create, you must set the default schema for the SQL Server login. Use the following command syntax for the BPMDB (Process Server) database:
```
CREATE DATABASE BPMDB COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE [BPMDB]
GO
CREATE USER [sqluser] FOR LOGIN [user1\password] WITH DEFAULT_SCHEMA=[BPMPS00]
GO
CREATE SCHEMA [BPMPS00] AUTHORIZATION [sqluser]
GO
EXEC sp_addrolemember 'db_ddladmin', 'sqluser';
EXEC sp_addrolemember 'db_datareader', 'sqluser';
EXEC sp_addrolemember 'db_datawriter', 'sqluser';
```
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.

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.