ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Clarity PPM: The Datamart Extraction job will not work when the SQL Server Agent is enabled in Microsoft SQL Server Management Studio

book

Article ID: 28455

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

The Datamart Extraction job fails with errors below in BG log:

Caused by: java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]The SELECT permission was denied on the object  
'sysjobs_view', database 'msdb', schema 'dbo'.

This error has also been seen on some scenarios:

 "The Server Principal "NIKU" is not able to access the database "msdb" under the current security context"

Cause

The Clarity administration account must be added to the SQLAgentUserRole role for "SQL Server Agent" in Microsoft SQL Server.

 

Environment

Release: Clarity PPM 15.8 and supported releases

Resolution

The SQL Server Management Studio tool can be used to add the Clarity administrator account to the SQLAgentUserRole role by using the following steps:



  1. Login as 'sa' user.
  2. Open SQL Server Management Console.
  3. Open the Security folder.
  4. Open Logins folder.
  5. Right click on user SQLSERVER2XXXSQLAGENTUSER.
  6. Select Properties.
  7. Select User Mapping.
  8. Check Map box for MSDB Database.
  9. Confirm that the roles db_owner and public are checked.
  10. Right click on the Clarity PPM schema owner, e.g. niku
  11. Select Properties.
  12. Select User Mapping.
  13. Check Map box for MSDB Database.
  14. Confirm that the roles db_owner and public are checked.
  15. Click OK to save changes.