How To Grant Permissions To Run Job in SQL Server

Before you begin this article, I suggest you read my article “How To Create a Login On SQL Server(Manage Logins)” for a detailed review of how a SQL Login was created and authorized.

A login must have authority over some database roles in the msdb database in order to access SQL Server Agent. These roles in the msdb database are as follows.

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

But when you grant these privileges to a login, it can changes something you don’t want to change. You can find details of these roles in the msdb database in the article “SQL Server Agent Fixed Database Roles“.

Sometimes you may need to grant authority to run only one job. In this case, you can create a stored procedure with the help of the following script, and you can meet your needs by granting the execute privilege on this stored procedure.

In order to grant execute stored procedure privilege to the login, we first grant public privilege in the msdb database. You can find details in the article “How To Create a Login On SQL Server(Manage Logins)“.

Then right-click the stored procedure that we created in the msdb database and click Properties.

Then go to the permission tab. Click Search and Browse to select the relevant login and click ok. Then we select the Execute authority and click Ok.

We’ve completed authorization. The corresponding login can start the job by running this stored procedure as follows.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *