locked
sql agent job access RRS feed

  • Question

  • I want to set up an sql server agent job and give someone access to run it (just this one job, none of the other existing jobs).    I don't want them to be able to modify the job, just want to give them access to run it only.    Is that possible?
    Wednesday, August 24, 2011 5:53 PM

Answers

  • Look at this article at my web site: http://www.sommarskog.se/grantperm.html

    I have examples of doing this in two different ways. I also carefully discuss the security considerations are.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Raj Lanka Friday, August 26, 2011 6:24 AM
    • Marked as answer by Stephanie Lv Friday, August 26, 2011 7:41 AM
    Thursday, August 25, 2011 9:53 PM

All replies

  • The lowest level of permissions you can give them is SQLAgentUserRole in MSDB; this allow them to Create/Modify/Delete the job they are owner of.  In addition they can view, enable/delete/execute jobs they own.  I don't know of a way to limit it more than that.


    Mohit K. Gupta --- MCTS, MCITP, MCC2011 http://sqllearnings.blogspot.com/
    Thursday, August 25, 2011 3:58 AM
  • You can create a SP that runs the job and You can use "with execute as owner" to run the SP as the database owner.Here, The user don't need the permission on sp_start_job.

    Create Procedure dbo.uspExecMyJob with execute as owner

    as

    sp_start_job @job_name = 'PlaceYourJobname'

     


    velmurugan.s
    Thursday, August 25, 2011 8:32 AM
  • Look at this article at my web site: http://www.sommarskog.se/grantperm.html

    I have examples of doing this in two different ways. I also carefully discuss the security considerations are.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Raj Lanka Friday, August 26, 2011 6:24 AM
    • Marked as answer by Stephanie Lv Friday, August 26, 2011 7:41 AM
    Thursday, August 25, 2011 9:53 PM
  • I think this will work for me.   Thanks everyone!

    Thursday, August 25, 2011 11:37 PM