Role to view SQL server job log

Question

We have a monitoring tool that can check if Microsoft SQL Server jobs have run successfully. What I want to do is create a user that has as little permission as possible but it must be able to watch the history log to see if the job completed.

What role should I assign the user?

Edit: The servers are Microsoft SQL Server 2005 to 2012

Answer 1

To be able to check all jobs you would need go add the user to the SQLAgentReaderRole in the msdb database which also gives that user permission to create jobs. To minimize the access granted you can also grant the user select permission on the tables used by the sql server agent

Answer 2

If "watch the history log to see if the job completed" can be accomplished by using either a stored procedure, scalar function, or table-valued function (as opposed to direct SELECT access to the msdb.dbo.sysjob* tables), then you can get away without granting any permissions at all to any real Login/User that will be logging in. You will still create at least a Login, and possibly also a User (but possibly not), but no person or app code will ever connect to SQL Server using this Login. The idea is to use module signing to grant permissions by proxy / indirectly. I detailed the method (well, the first part for this particular usage) in the following answer:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

A difference between this case and the one in that answer is that instead of a server-level permission needed to view DMV info, this case here needs a database-level role. So, the additional steps beyond what is discussed in that linked answer are:

1. No need to grant the VIEW SERVER STATE server-level permission to the Login
2. Restore the Certificate also to msdb
3. Create a User in msdb from that Certificate
4. Grant the User in msdb the database role of SQLAgentReaderRole

You can create either a stored procedure or scalar function that accepts a parameter for the Job Name if you want to check just one job at a time. Or you can create a table-valued function if you want to get the status of multiple jobs. In both cases you provide the query in that stored procedure or function and grant access to whomever on only that stored procedure or function. This method allows you to give access to that query only and not anything else implied by the SQLAgentReaderRole role.

Or, you could allow for more free-form ad hoc query access by creating a table-valued function per each table that is needed for any desired queries (or maybe some JOINs can be done in some of the TVFs) and signing those. Then whomever you grant SELECT permission to on those TVFs can do their own queries. The only downside is that the TVFs need to be multistatement and cannot be inline TVFs, but for this particular scenario that should be fine in most cases since these queries shouldn't be running multiple times per second ;-).