Grant a user access to job history

Question

Grant a user access to job history

toyabre

SSC Enthusiast

Points: 173
More actions
January 23, 2020 at 7:48 pm

#3717874

Hello.
I would like to give a user the right to see the history of the jobs on an instance.
I created the login and then the user in msdb. I granted the user SQLAgentReaderRole.
When he connects, he can see the jobs properties but he can't see the history. Job History is not clickable.
Can you please help me with the best way to grant him access to the jobs history?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

According to Microsoft's Agent roles documentation, ReaderRole should be enough for them to be able to view history:

https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-2016#sqlagentreaderrole-permissions

If all they need is ability to view history, and not the ability to create their own jobs, then maybe the easiest thing to do is to create a stored procedure that has the query in it to report on a job history, and then just grant them execute rights on that.

All of the built in roles, including the least privileged SQLAgentUserRole have the ability to create their own jobs. (why ReaderRole is more privileged than UserRole I'll never understand)

This reply was modified 4 years, 3 months ago by Chris Harshman.

toyabre SSC Enthusiast Points: 173 More actions · Answer 2

Thanks for replying Chris. But testing it, the user can see the job and his properties, but can't see the history. Do I need to grant access to a view or something like this?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

It's already granted to the role. Haven't checked for awhile but for the history it should execute sp_help_jobhistory when checking the history in SSMS. Is the user using a current version of SSMS? Were any of the default permissions in msdb changed?

Sue

frederico_fonseca SSCoach Points: 16017 More actions · Answer 4

not being able to see the history may be other permissions being set or SSMS having issues.

or... too many jobs on the server and the history for the jobs he wishes to see is getting deleted before he/she manages to see it

Regarding the role - it really is not just a Reader and should be tightened

I normally create a new role SQLAgentReadOnlyRole as follows

use msdb
create role SQLAgentReadOnlyRole

alter role SQLAgentReaderRole add member SQLAgentReadOnlyRole
deny execute on dbo.sp_add_job to SQLAgentReadOnlyRole 
deny execute on dbo.sp_add_jobschedule to SQLAgentReadOnlyRole
deny execute on dbo.sp_add_jobserver to SQLAgentReadOnlyRole
deny execute on dbo.sp_add_jobstep to SQLAgentReadOnlyRole
deny execute on dbo.sp_add_jobstep_internal to SQLAgentReadOnlyRole

and then add any AD group to this new role as required