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

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.