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:
- No need to grant the
VIEW SERVER STATE server-level permission to the Login
- Restore the Certificate also to
- Create a User in
msdb from that Certificate
- Grant the User in
msdb the database role of
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
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 ;-).