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
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
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.
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. |