Datamart job extraction fails due to security permissions with MS sql

[Stefano Lazzarotto]

Hi team.

Datamart job fails with error:
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'.

I know that there are some sql grants to add to niku database user but customer doesn't want to add those grants for security reasons.
If I understand correctly, when sql server agent is active, clarity datamart job tries to parallel the executions and fails because niku user doesn't have the necessary grants to create sql jobs.

Do you know if there is a way/workaround/setting to avoid this problem?

Thanks and regards.
Stefano

[Vincent Frimpong]

Hi Stefano,

See this KB article: https://knowledge.broadcom.com/external/article?articleId=28455

Regards

Vincent

[Stefano Lazzarotto]

Thanks Vincent. I already checked and dba doesn't want to give those grants.

Ottenere Outlook per Android<https: aka.ms/aab9ysg="">
::DISCLAIMER::
________________________________
The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects.

[Suman Pramanik]

These are product requirement, any reason why DBA doesn't to grant and in order to make it working that is needed and any changes in those area if needed needs a change in product

------------------------------
Thanks & Regards
Suman Pramanik
Sr. Principal Support Engineer | Customer Success & Support, Enterprise Software Division
Broadcom
------------------------------

[Paul Schofield]

I did it with a script:

USE msdb

CREATE USER ppm for LOGIN ppm (this is the logon for Clarity)

ALTER ROLE sqlagentuserrole ADD MEMBER ppm

Can you ask the dba to execute that instead perhaps?

[Navdeep Joshi]

What about this - almost the same as what Vince provided:
https://knowledge.broadcom.com/external/article/50306/clarity-any-clarity-job-will-not-work-wh.html

I guess, you will need to pick one of the options - from Vince's article or, from the above :)

------------------------------
Regards
NJ
------------------------------

[Stefano Lazzarotto]

Thanks to all.
Still waiting an answer from DBA but they are complaining about security (niku user accessing to other database) and, as per their policy,  sql agent job aren't permitted. They have only some jobs for backup and internal stuff.
I'll chek with dba if there are other possibilities.
Regards
Stefano