Creating users and schemas for SQL Server databases

Edit online

Draft comment:
This topic only applies to BAW, and is located in the BAW repository. Last updated on 2025-01-20 10:38

Draft comment:
This topic was viewed 47 times since its publication

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

Before you begin

Assign the IBM® Business Automation Workflow database user to the following roles:

Note: The database must be created by the database administrator who can then assign these roles to the database users for IBM Business Automation Workflow.

db_ddladmin
db_datawriter
db_datareader
db_securityadmin
bulkadmin

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, if SQL Server Authentication mode is selected, 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 do the following steps to create users and schemas.

Procedure

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 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.
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 to update the master database (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
```

For each database that you create, you must set the default schema for the SQL Server login (except for the Content database (CPEDB), where you already set the schema in Creating SQL Server databases).

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';
EXEC sp_addrolemember 'db_securityadmin', 'sqluser';
EXEC sp_addsrvrolemember 'login_account', 'bulkadmin';
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.