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

Document ID : KB000028455
Last Modified Date : 14/02/2018
Show Technical Document Details

Symptom:

The Datamart Extraction job fails with errors below in niku-bg.log when the SQL Server Agent is enabled in Microsoft SQL Server
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'.
at com.ca.clarity.jdbc.base.BaseExceptions.createException(Unknown  Source)
at com.ca.clarity.jdbc.base.BaseExceptions.getException(Unknown Source) 
<<remaining error message lines omitted>> 

Solution:

The CA PPM administration account must be added to the SQLAgentUserRole role (Microsoft SQL Server) in order to use the "SQL Server Agent" in Microsoft SQL Server. These users can create jobs and manage only jobs that they created.

The SQL Server Enterprise Manager tool can be used to add the CA PPM administrator 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 CA PPM schema owner, for example "niku" or "clarity".
  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.