IBM Support

Error 'RQP-DEF-0326 User defined SQL is not permitted for the user who has the identity...' when running Controller Standard Reports

Troubleshooting


Problem

User launches Controller. User attempts to run a standard report (for example 'Reports - Company Journals') and clicks on 'preview'. User receives error message.

Symptom

Example - Cognos Analytics:
image-20190926070428-1
  
Example - Cognos BI:
 
RQP-DEF-0326
User defined SQL is not permitted for the user who has the identity '{Everyone, All Authenticated Users, Consumers, Metrics Authors, Metrics Users, Planning Contributor Users, Controller Administrators, Controller Users, Data Manager Authors, Adaptive Analytics Users, None}'.

German:
Cognos 8
RQP-DEF-0326
Benutzerdefiniertes SQL ist nicht zugelassen fur den Benutzer mit der Identitat '{Alle authentifizierten Bunutzer, Jeder, Konsumenten, Metriken-Autoren, Metriken-Benutzer, Cognos Controller-Benutzer, Data Manager-Autoren, C8Controller-U, CERTSVC_DCOM_ACCESS, Cognos-Settings, Cognos-G, Domanen-Benutzer}'.
Details
OK
Company Journals by Journal Type
HTML

Cause

The user does not have sufficient Cognos Analytics (or Cognos BI) permissions to view the reports.
   
More Information
By default, each Controller user will belong to the "Controller Users" CA role. Therefore, when the CA (or BI) administrator performs security modification tasks, care must be taken to ensure that the "Controller Users" group still has sufficient permissions to perform their tasks.

Environment

Controller configured to use 'Cognos' (CAM) authentication (as opposed to the default "native" authentication).

Resolving The Problem

Modify the Cognos (CAM) security permissions inside Cognos Connection as appropriate to the design of your system.

  • TIP: Check with your IBM Cognos Analytics (CA) or Cognos BI administrator/consultant for more information.

Steps:
There are several different methods that can be used.
  • IMPORTANT: Make sure that you check with your CA security administrator to find out which one is best for your needs.

Method #1 (easiest for most environments)
In many environments, the simplest solution would be to:
1. Launch Cognos Administration website 
  • Assuming you are using a modern version of Controller, then you will be using Cognos Analytics.
  • Therefore: the website is:       http://<servername>:9300/bi/v1/disp?b_action=cogadmin
  
[If you are using an older version of Controller, integrated with Cognos BI, then instead you would launch this website instead:
http://<cognosbiserver>/ibmcognos]
  
 
2. Navigate to the 'Security' section
3. Open the 'Cognos' namespace
4. Add the group 'Controller Users' to the group 'Authors':


TIP: For more information, see separate IBM Technote #1347354.

Method #2
For some customer environments, it is best to manually assign the user (or group that the user is a member of) the following permissions:
  • User Defined SQL capability
  • grant execute permissions
  • also "Traverse" permission on the "Report Studio" capability

Steps:
1. Launch Cognos Administration website 
  • Assuming you are using a modern version of Controller, then you will be using Cognos Analytics.
  • Therefore: the website is:       http://<servername>:9300/bi/v1/disp?b_action=cogadmin
  
[If you are using an older version of Controller, integrated with Cognos BI, then instead you would launch this website instead:
http://<cognosbiserver>/ibmcognos]

2. Click on the to security tab
3 .Click Capabilities, and beside the capability "Report Studio" click Set Properties


4. Click the Permissions tab.
5. Add both of the groups "Controller Users" and "Controller Administrators":

6. Modify their permissions so that "Traverse" has has 'Grant' ticked/enabled

7. Click OK to return to the Capability screen.
8. Click the "Report Studio" link to see the list of sub-capabilities
9. Click "Set Properties" beside User Defined SQL.
10. Click the Permissions tab.
11. Add both of the groups "Controller Users" and "Controller Administrators":


12. Modify the settings so that both of these groups have 'grant' ticked/enabled for the right "Execute"
13. Click OK to return to the Capability screen
14. 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":"8.5.1;8.5;8.4;8.3;10.1;10.1.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

1035644

Document Information

Modified date:
26 September 2019

UID

swg21371229