Grant a User to run only 1 specific SQL Server Agent job and also specifying to start running the job at a particular step?

techresearch7777777 1,766 Reputation points
2020-11-30T21:01:50.597+00:00

Hello how can I grant a User to run only 1 specific SQL Server Agent job and also specifying to start running the job at a particular step?

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,676 questions
0 comments
Accepted answer
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2020-12-01T09:02:57.127+00:00

    Hi @techresearch7777777 ,

    You can create a stored procedure in the MSDB database to let a user to execute a job.
    In addition, there are only two ways that someone can have permission to execute a SQL Agent job. You must either own the job, or be a member of the role SQLAgentOperatorRole (MSDB).
    Please refer to the following articles which might help:
    How do I let someone execute a SQL Agent job they don’t own
    Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2020-11-30T23:01:38.023+00:00

    I discuss a possible options to do this here: http://www.sommarskog.se/grantperm-appendix.html#startjobs. It works with a combination of a certificate signing and EXECUTE AS. Yes, you will have to read some prerequisites to fully enjoy this particular chapter.

    1 person found this answer helpful.
    0 comments
  2. techresearch7777777 1,766 Reputation points
    2020-12-01T20:53:39.81+00:00

    Thanks for the replies.

    Yes what worked for me is I made the login owner of the job and then created user with same name in msdb and put in the SQLAgentOperatorRole role.