1

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) SELECT [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.

0

The SQLAgentUserRole comes with a lot more rights than just read on the two tables in your query, you can view these rights on the securables tab in the properties for the role. If you want to get access to just the two msdb tables in your query then you would create another database role with only those rights and then add your SQL/AD credentials to the instance with that role in msdb.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.