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 users 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: 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 SqlJDBCXAUser role.
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 or the user who created the database (the database owner). This is because the default schema for the super user is dbo and this cannot be changed.

About this task

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

Procedure

  1. Complete one of the following steps:
    • If you are using SQL authentication, create a SQL Server login account for the database user of each of the databases that you specified in the IBM BPM deployment environment configuration. For example, if sqluser is the database user name and secret1 is the password for sqluser, use the following command syntax:
      USE MASTER
      GO
      CREATE LOGIN sqluser WITH PASSWORD='secret1'
      GO
    • If you are using Windows authentication, create the SQL Server login account for your Windows machine using the login information for the machine. For example, if domain1 is the domain name and user1 is the user name for your Windows machine, use the following command syntax:
      USE MASTER
      GO
      CREATE LOGIN [domain1\user1] FROM WINDOWS
      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.
  2. Update the master database to grant permission for XA transactions for the login account (but first ensure that you have successfully completed the instructions in the topic "Configuring XA transactions"). For example, if sqluser is the database user name, use the following command syntax (where login_account is the same login account that you used in the previous step):
    USE MASTER
    GO
    CREATE USER sqluser FOR LOGIN login_account WITH DEFAULT_SCHEMA=sqluser
    GO
    EXEC sp_addrolemember N'SqlJDBCXAUser', N'sqluser';
    GO
  3. For each database that you create, you must set the default schema for the SQL Server login. For example, if BPMDB is the database name, and sqluser is the database user name, use the following command syntax (where login_account is the same login account that you used in the previous step):
    USE BPMDB
    GO
    CREATE USER sqluser FOR LOGIN login_account WITH DEFAULT_SCHEMA=sqluser
    GO
    CREATE SCHEMA sqluser AUTHORIZATION sqluser
    GO
    EXEC sp_addrolemember 'db_ddladmin', 'sqluser';
    EXEC sp_addrolemember 'db_datareader', 'sqluser';
    EXEC sp_addrolemember 'db_datawriter', 'sqluser';
    GO

What to do next

When you create database schemas 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. For information about the database privileges that are needed to access the data stores, see the topic SQL Server database privileges.