I often check the status of jobs to gain information about schedules, run status, run times, etc. via query against msdb.dbo tables in team environments as a part of ETL validation. I am curious if permissions can be granted to a universal login utilized by devs in non-isolated environments, such as Merge or QA where multiple teams are accessing, leading up to but not including Prod to allow viewing of msdb.dbo tables?
Currently, the said universal dev login does not have sufficient privileges and must rely on DBA’s to report this information as requested, consuming their time and effort. I do not believe this is the same as granting SQLAgentUserRole, but could be mistaken. Is this request overreaching / stepping on DBA toes?
My query for reference: (using JDBC via SoapUI requires higher verbosity)
[sysjobs].[name] AS [job_name],
[sysjobhistory].[run_date] AS [run_date],
[sysjobhistory].[run_time] AS [run_time],
[sysjobhistory].[run_status] AS [run_status]
FROM [msdb].[dbo].[sysjobs] INNER JOIN [msdb].[dbo].[sysjobhistory]
ON [sysjobs].[job_id] = [sysjobhistory].[job_id]
WHERE [sysjobs].[enabled] = 1 -- only enabled jobs
ORDER BY [run_date], [run_time] DESC
Thank you for your time.