Datamart Extraction job will not work when the SQL Server Agent is enabled for the Clarity user

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"

This applies to Clarity used with Microsoft SQL Server (MSSQL)

Environment

Release: All

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