1

The Microsoft SQL Server database role SQLAgentReaderRole apparently lets a member create SQL Agent jobs along with viewing jobs, job steps, and results.

We need to allow a user to see all SQL agent jobs, steps, schedules, and results, but not create any jobs. This can almost be provided using several different views, but the results are messy and hard to read -- I want to be able to see the SQL Agent node in Management Studio and verify things.

Is there any role or permission that would allow this? Let's assume SQL 2008 through SQL 2016. Thanks.

1 Answer 1

3

Seems like this should work:

The "reader" name is definitely misleading. This may be stating the obvious, but to get around this I've granted the SQLAgentReaderRole role and explicitly denied EXECUTE on add job sprocs in msdb for the logins with the role.

DENY EXECUTE ON [dbo].[sp_add_job] TO [YourLogin] GO 
DENY EXECUTE ON [dbo].[sp_add_jobschedule] TO [YourLogin] GO 
DENY EXECUTE ON [dbo].[sp_add_jobserver] TO [YourLogin] GO 
DENY EXECUTE ON [dbo].[sp_add_jobstep] TO [YourLogin] GO 
DENY EXECUTE ON [dbo].[sp_add_jobstep_internal] TO [YourLogin] GO
1
  • It SHOULD not be this hard. In the future, we might have to remember to DENY additional things. But, this is a great answer to "the way things are". Thank you.
    – DWalker07
    Dec 11, 2017 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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