IBM Support

How to change the SQL login for Controller, by modifying the tables, views and stored procedures to be owned by 'dbo' (typically for SQL 2014 onwards)

Troubleshooting


Problem

I.T. department's database administrator ('DBA') wishes to change the user (the 'SQL login') which the Controller application server uses to connect to the SQL database. In other words, they want to modify the value of 'user ID' (inside Controller Configuration) so that the Controller system uses a different SQL login user to connect to its application repository database. However, when they try to do this they get an error. This is triggered by the fact that the database table owner is not set to 'dbo'. What does the customer need to do (for example change the properties of the table owners) for this database to work correctly with the new SQL login user?

Symptom

Example:

Imagine a scenario where the following is true:

  • The customer has an existing database (called 'RC_fastnet_old') which has all its tables (plus 'stored procedures' and 'views') owned by the user 'fastnet':
  • Customer uses the user ID (SQL login) 'fastnet' to connect (to this database):
  • This works OK.

In other words, when the administrator launches 'Database Conversion Utility' and press 'Connect':
  • The 'Upgrade to' version appears successfully (typically the same number as the 'current version')
  • There is no error message seen.

Afterwards:

  • The customer creates a duplicate copy of that database (called 'RC_cognos_new')
  • Customer creates a new SQL login (called 'cognos') on the SQL server
  • Customer tries to connect to the database, using this new user:


This fails with an error message:

    ** ERROR: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "RC_cognos_new" requested by the login. The login failed.

Cause

In older versions of SQL (SQL 2000 and earlier), when database tables, stored procedures and views were created, they were 'owned' by the SQL login that was used to created them.

  • For example, if the SQL login was 'fastnet' then the database would look similar to:

and:

and:

In more modern versions of SQL (typically from SQL 2005 onwards) Microsoft changed how owners and schemas worked.

  • This meant that (when database tables, stored procedures and views were created) they are now owned by 'dbo':
  • This makes it much easier to modify the SQL login used.

================================

Summary:

If your database tables/views/stored-procedures are owned by 'dbo' then it is incredibly easy to change the SQL login.

  • Simply make sure that the new SQL login is a dbowner of your database.

If your database tables/views/stored-procedures are owned by a specific SQL login (for example 'fastnet') then you need to perform some extra steps to convert the database to use 'dbo'.

================================

Environment

This Technote is based/tested on Controller 10.2.1 and SQL 2014.

  • For earlier versions of SQL, it was possible to use a different method (for more information, see separate IBM Technote #1442534).
  • However, the method explained in this Technote should work for any version of SQL from 2005 onwards.

Resolving The Problem

Modify all Controller database tables, stored procedures and views so that they are owned by 'dbo' (not a SQL login for example 'fastnet')

  • Afterwards, configure Controller to connect to the database using a SQL login that connects as 'dbo'.

Steps:
The following steps are based on SQL 2014, although they should work for all currently-supported versions of SQL.

PART ONE: Converting the tables, views and stored procedures to be owned by 'dbo':
TIP: This section can be skipped if your tables, views and stored procedures are already owned by 'dbo'.

1. Launch 'SQL Server 2014 Management Studio'
2. As a precaution, create a backup copy of the database that you wish to modify (for example 'RC_cognos_new')

3. Right-click on the database, and choose 'New Query':


4. Paste the following script:
    SELECT 'ALTER SCHEMA dbo TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
    FROM sys.Objects DbObjects
    INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
    WHERE SysSchemas.Name = 'fastnet'
    AND (DbObjects.Type IN ('U', 'P', 'V'))

=============================
IMPORTANT: Replace the entry 'fastnet' with the name of your original SQL login.
=============================

5. Press 'Execute':


6. Inside the 'Results', left click on the top-left (blank/empty) square
  • This will select all the results (everything will turn blue).

7. Right-click on the blue/highlighted results, and choose 'Copy':


8. Delete the current contents of the 'script' section
9. Paste the contents of your clipboard into the script section, for example:


10. Press 'Execute'
  • You should get a success message: Command(s) completed successfully.

11. Close 'SQL Server 2014 Management Studio'
12. Re-launch 'SQL Server 2014 Management Studio'
13. Expand your database
14. Check that the tables, views and stored procedures have all changed so that the owner is now 'dbo':






PART TWO: Changing the SQL login used by Controller:

15. On your SQL server, ensure that the new SQL login (for example 'cognos') exists

16. Inside 'Server Roles' make sure that the only roles ticked are:
    • bulkadmin
    • public


17. Inside 'User Mapping' make sure that the only roles ticked are:
  • db_owner
  • public


18. Inside 'Controller Configuration' make sure that the connection uses the new SQL login, and test:

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

1037777

Document Information

Modified date:
15 June 2018

UID

swg21364881

Page Feedback