IBM Support

Microsoft SQL Server: Tables get generated in the 'dbo' schema.

Troubleshooting


Problem

Tables and other objects get generated in the dbo schema instead of the schemas specified in the setup.cfg file.

Cause

This issue occurs when the SQL server's default schema is mapped to dbo.
Once the users and schemas are created in SQL server, the DBA must point the schemas to the respective users.
Note: PEM only supports configurations using the default schema associated with the respective database users.

Diagnosing The Problem

This issue is diagnosed by verifying if the schemas are correctly mapped to the users created in the SQL database server.

Resolving The Problem

Perform the following steps as a prerequisite before PEM migration:

  1. Create the database pemtest and assign the owner.
  2. Create logins for two schemas - pemusr01 and pemusr02.
  3. Create two users for the corresponding logins.
  4. Create two schemas with the names - pemusr01 and pemusr02.
    Note: You must ensure that the schema name matches the login name in Step 2.
  5. Map the schemas to the users by assigning the default schema from dbo to pemusr01/pemusr02. To do this, perform the following:
    1. In SQL Server Management Studio (SSMS), navigate to the Object Explorer.
    2. Select your database.
    3. In the Database and Security window, under the General tab, select the ellipses (…) for Default Schema.
    4. In the Browse for Objects window, select the user to map their schema to the default schema dbo.
    5. Click OK.

Note: Step 5 resolves the issue by creating tables and other objects in the pemusr01 and pemusr02 schemas instead of the standard dbo schema.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSFG8Z","label":"IBM Sterling Partner Engagement Manager Software"},"ARM Category":[{"code":"a8m0z000000boKAAAY","label":"Deployment"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
12 January 2024

UID

ibm17105446

Page Feedback