IBM Support

"Msg 208, Level 16, State 1, Procedure usp_triggerimportbatchjobs, Line 44 Invalid object name 'zspec' when running exec dbo.usp_triggerimportbatchjobs

Troubleshooting


Problem

It administrator runs an import from staging tables function, by running the following: exec dbo.usp_triggerimportbatchjobs An error appears.

Symptom

Msg 208, Level 16, State 1, Procedure usp_triggerimportbatchjobs, Line 44 Invalid object name 'zspec'

Cause

The SQL login used in the stored procedure (for example 'dbo') cannot read the data inside the Controller database table "zspec".

Environment

Invalid SQL login configuration, relating to the owners of both the database tables and also the stored procedure.

Example

  • Imagine a scenario where the Controller database tables owned by a SQL user called 'controller'
  • Controller database stored procedures owned by user 'dbo'

For a visual description of this scenario, see below:

This problem occurs if the owner of 'tables' and 'views' and 'stored procedures' are not the same user.
  • In other words, this Technote is caused by the owner of 'tables' not being the same as 'stored procedures'.

Diagnosing The Problem

The following steps are based on SQL 2005:
1. Log onto the SQL Server as an administrator
2. Launch "SQL Server Management Studio"
3. Locate the Controller database, and expand 'Tables'
4. Make a note of the owner (for example 'fastnet' or 'dbo') of these tables.

  • TIP: The owner is the first word in the table name *before* the full-stop (.)
  • For example, table 'dbo.CRDARCRES' is owned by 'dbo'.

5. Expand 'Views' and make a note of the owner of the owner
6. Expand 'Programmability - Stored Procedures' and make a note of the owner of the owner.

Resolving The Problem

Ensure that all Controller database tables, stored procedures and views are owned by the same SQL login (for example 'fastnet' or 'controller' or whatever).

Steps:


WARNING: Before continuing, as a precaution please ensure the database has been backed up.

NOTE:
  • The following method generates SQL statements which contain pre-formatted sp_changeobjectowner scripts for each user table, stored procedure and view in the database
    • The method then executes these scripts to change the owner
  • The following instructions are based on SQL 2005 and 2008.
    • In the unlikely event you are using SQL 2000, see separate IBM Technote #1335618.
  • Please change all references to 'username_to_be_used' to the new object owner (for example 'controller' in Scenario #1 above)
  • To be clear username_to_be_used is preceded by two single quotes and then three single quotes.

** PART ONE : Creating a new schema
Let us assume that you want to use a SQL login called "cognos".
  • Naturally, please change the instructions your your preferrred SQL login.

1. Ensure no users are in Controller
2. Log onto the SQL Server as an administrator
3. Launch "SQL Server Management Studio"
4. Right-click on the Controller database, and choose Expand the section ‘Security’
5. Notice how there is a user called "cognos", but there is NOT a schema called "cognos":

6. Right-click on ‘Schemas’ and choose ‘New Schema’
7. Fill in both the ‘Schema name’ and ‘Schema owner’ sections with ‘cognos’ (or the name of your SQL login), and then click OK
8. It should look similar to this now:


TIP: If you do not perform the above steps, then when you try the steps inside 'part two' you will get the following error message:
    Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 111
    Database principal or schema 'cognos' does not exist in this database.

9. Open "Users" and double-click on "cognos"
10. Change the "default schema" from the current (dbo) to "cognos":



11. Finally, to remove orphaned users, right-click on the Controller database, and choose 'New Query'
12. Copy the following into the blank/white window.
    EXECUTE sp_change_users_login 'Update_One', 'cognos', 'cognos'
    GO

Again, remember to change *both* entries of "cognos" to whatever your SQL login is called.

13. Click "Execute".

** PART TWO : Changing the database table owner

1. Right-click on the Controller database, and choose 'New Query'
2. Copy and paste the following text into the blank/white window:
    select 'exec sp_changeobjectowner [' + rtrim(c.name) + '.' + rtrim(a.name) + '],
    ''username_to_be_used'''
    from sysobjects a, sysusers c where a.type = 'U' and a.uid = c.uid

IMPORTANT: Make sure that you have changed the entry username_to_be_used to be your SQL login (for example cognos)

3. Click "Execute". You will see many new entries appear inside the 'results' Window.
4. Delete the text (SQL statements) from the Window that we have been using (so it is now blank again)
5. Right-click on the 'results' Windows and choose select all (to highlight all items in the results grid)
6. Right-click and choose copy, to copy all of the the resulting text (from the results grid) and then paste it into the "query" Windows
7. Click "Execute" (to run all these multiple scripts).

** PART THREE : Changing the database stored procedure owner

1. Right-click on the Controller database, and choose 'New Query'
2. Copy and paste the following text into the blank/white window:
    select 'exec sp_changeobjectowner [' + rtrim(c.name) + '.' + rtrim(a.name) + '],
    ''username_to_be_used'''
    from sysobjects a, sysusers c where a.type = 'P' and a.uid = c.uid

IMPORTANT: Make sure that you have changed the entry username_to_be_used to be your SQL login (for example cognos)

3. Click "Execute". You will see many new entries appear inside the 'results' Window.
4. Delete the text (SQL statements) from the Window that we have been using (so it is now blank again)
5. Right-click on the 'results' Windows and choose select all (to highlight all items in the results grid)
6. Right-click and choose copy, to copy all of the the resulting text (from the results grid) and then paste it into the "query" Windows
7. Click "Execute" (to run all these multiple scripts).

** PART FOUR : Changing the database view owner

1. Right-click on the Controller database, and choose 'New Query'
2. Copy and paste the following text into the blank/white window:
    select 'exec sp_changeobjectowner [' + rtrim(c.name) + '.' + rtrim(a.name) + '],
    ''username_to_be_used'''
    from sysobjects a, sysusers c where a.type = 'V' and a.uid = c.uid

Make sure that you have changed the entry username_to_be_used to be your SQL login (for example cognos)

3. Click "Execute". You will see many new entries appear inside the 'results' Window.
4. Delete the text (SQL statements) from the Window that we have been using (so it is now blank again)
5. Right-click on the 'results' Windows and choose select all (to highlight all items in the results grid)
6. Right-click and choose copy, to copy all of the the resulting text (from the results grid) and then paste it into the "query" Windows
7. Click "Execute" (to run all these multiple scripts).

Visual_demonstration_of_SP_owned_by_DBO.jpg

[{"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":"8.5","Edition":"All Editions","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21442534

Page Feedback