User clicks 'Maintain - Rights - Reports'. The 'User Rights and Security Group Reports' selection box appears. User ticks/selects (checks) the option 'Menu Groups' (inside the Security Group Reports' sectuon). User clicks 'Preview' button.
When the 'User Rights and Security Group Reports (Preview)' windows appears, user receives error message.
The problem only affects some (very few) standard reports.
- In addition, it only affects some selections inside those standard reports.
The problem is seen if the user ticks the option ' Menu Groups' inside 'User Rights and Security Group Reports':
[HOWEVER, if you
only tick 'User Groups' and 'Users' (in other words, you do
not tick 'Menu Groups'), then the report will run successfully].
Below is the error seen (when you tick ' Menu Groups'):
User Rights and Security Group Reports (Preview)
IBM Cognos 8 Help
X An error occurred while performing operation 'sqlPrepareWithOptions' status='-56'.
UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server] Invalid object name 'cognos.crdConnectedUsersSub'. (SQLSTATE=42S02, SQLERRORCODE=208)RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'QueryConnectedUsers' is not defined or its query items contain unresolved references.RSV-SRV-0042 Trace back:....
Code production problem (reference APAR PK98851) in Controller, where it does not fully allow the use of 'dbo' as a table owner.
The problem is demonstrated by the following part of the error message: Invalid object name 'cognos.crdConnectedUsersSub'
- The report is looking for a table 'cognos.crdConnectedUsersSub' when in fact the table's name is 'dbo.crdConnectedUsersSub'
In other words, for a *very small* number of reports/options, Controller (incorrectly) looks for a table called ' <sql_login_name>.xxxx' instead of ' dbo.xxxxx'.
- For example, it may incorrectly look for "cognos.xxxx" if your SQL login name is 'cognos'.
Controller database tables are owned by "dbo".
- In other words, all the tables (inside "SQL Server Management Studio" are preceded by the name 'dbo' (see below):
There are several scenarios where this occurs:
- Scenario #1 (RARE) - Customer using SQL 2000, and is using a 'system administrator' (SA) account.
For example, the problem will affect both the following two separate scenarios:
(1) customer using the built-in SQL login 'sa'
(2) or customer using a custom (created) SQL login (for example 'cognos') but has mistakenly given this 'sysadmin' rights
- This is not a recommended method of using SQL logins
- instead, the customer should have created a customer SQL login (for example 'cognos') and given it DBOWNER rights to the database(s).
- Scenario #2 (LIKELY) - Customer using SQL 2005 or 2008
- This is because Microsoft has changed (from SQL 2005 onwards) some fundamental ways that SQL handles users/schemas. Specifically, schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.
- For more details, see third-party (Microsoft) information.
Resolving the problem
Upgrade to Controller 10.2.1 (10.2.5100.95) or later.
There are two different methods that you can perform to workaround the problem:
- Method #1 - Create a new user (schema) plus three views which directly point to the actual data tables.
- Method #2 - Change the owner of the tables (inside the Controller SQL database) from the current ("dbo") to a 'standard' SQL login (for example "fastnet" or "cognos")
- Although you only need to change the table's owner to fix this problem, other problems will occur unless you change the owners of other SQL objects
- Specifically, you will need to change the owner of the 'stored procedures' and 'views' too.
(1) Method #1 - Easiest for most customers
Create a new user (schema) plus three views which directly point to the actual data tables.
Let us assume that:
- The Controller database is called "ControllerLive"
- The current user (connecting to the database) is "sa"
- The future user (that you want to use to connect to the database) is "cognos"
- The SQL server does not already have a user called "cognos" created
1. Obtain a short period of downtime (no users on the system)
2. Create a new SQL login called "cognos" with server roles " bulkadmin" and " public":
3. Inside "User mapping" tick the 'map' box for "ControllerLive" and ensure that " db_owner" and " public" are the only options ticked
4. Run the following SQL script (naturally you should modify this script if your user/database-name is different):
*** Workaround code to allow a Controller User to run the System User Group ***
*** Security Reports correctly and avoid the APAR 98851 ***
*** A schemea called cognos is created and the main SQL User is its owner ***
*** Three Views are then created to simply select all data from the ***
*** corresponding dbo.crd... tables ***
USE [ ControllerLive]
/*** Create cognos schema and assign user cognos as its owner ***/
/*** Change user to your actual username i.e. fastnet if applicable ***/
CREATE SCHEMA [ cognos] AUTHORIZATION [ cognos]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*** Create three views using the cognos schema to read all from corresponding table ***/
CREATE VIEW [ cognos].[crdConnectedUsersSub]
CREATE VIEW [ cognos].[crdJournalTypesSub]
CREATE VIEW [ cognos].[crdSecurityItemsSub]
/*** REMEMBER TO DROP THE VIEWS AND THE SCHEMA BEFORE YOU UPGRADE THE DATABASE ***/
/*** RERUN THIS CODE AFTER THE DATABASE UPGRADE UNTIL APAR PK98851 IS FIXED ***/
5. Modify the database connection so that it uses the new user (cognos) and save changes:
7. Click the green " tick" button
8. Click " Repair"
(2) Method #2 - Probably more complicated for most customers
- Although there are no foreseen problems with using this work around, IBM cannot be held responsible for any problems that may result from the use of the following instructions!
=> use the following at your own risk!
- The following instructions will only work on 'standard' configured SQL server systems.
- You may need to modify the instructions to work on your environment!
- If you do want to try this work around, then it is recommended that you try it on your 'test' database first (perform a 'before' and 'after' test) before trying it on "live".
1. Check what the name of the "SQL login" user is, that the application server is using to connect to the database with.
TIP: You can check this by either:
- (a) Launching Controller Configuration on your Controller application server, and looking at the 'database connection'
- or (b) Reading the error message carefully (for example, look for the text such as ''Invalid object name 'cognos.crdConnectedUsersSub'). In this example, you can see that the SQL login name is 'cognos'.
2. Ensure no users are using Controller (period of downtime)
3. Create a backup of the SQL database, as a precaution
4. Change the owners of the 'tables', 'stored procedures' and 'views' from the original name (typically " dbo") to the SQL login name (for example ' cognos' or 'fastnet').
- For instructions on how to do this, see separate IBM Technote #1442534 - see link below.
1371122 - SELECT permission denied on object 'sysobject
1346981 - 'ControllerProxyClient' error when saving new
1347969 - Error -2147217865 Cannot drop the table 'xmen
1345609 - Cannot determine Version of Database although
1366714 - Unable to determine database version. Invalid
1442534 - "Msg 208 Level 16 State 1 Procedure usp_tr
APAR PK98851 MAINTAIN\RIGHTS\REPORTS THEN CLICK PREVIE
Third Party (Microsoft) - User-Schema Separation