Clarity PPM: 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
Issue:

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'.

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

       
      Resolution:

      The SQL Server Management Studio tool can be used to add the Clarity administrator adccount 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.