Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 | use msdb GO create procedure dbo.execute_job with execute as owner as exec sp_start_job @job_name = 'MyJob' |
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.
1 | exec msdb.dbo.execute_job |
