Possibly save 2 hours of your time:
Recently, I was asked to find a generic way for non-technical administrators to be able to trigger or execute SQL Server Agent Job on their own on demand.
One solution is to build a windows authenticate administration website within our network domain. This web application will use windows authentication authenticate against specific windows user/group that has permission to trigger/execute a back end job.
public Exception ExecuteSqlAgentJob(string connectionString, string jobName)
Exception exception = null;
var dbConn = new SqlConnection(connectionString);
var execJob = new SqlCommand();
execJob.CommandType = CommandType.StoredProcedure;
execJob.CommandText = “msdb.dbo.sp_start_job”;
execJob.Connection = dbConn;
catch (Exception ex)
exception = ex;
The application pool identity that runs this application should have access to:
- the database which the SQL job targets
- Go to specific database >> Security >> Users >> add login
- msdb database
- Go to msdb database >> Security >> Users >> add login
- And give it SQLAgentOperatorRole membership
- This gives permission to execute dbo.sp_start_job
- Note: Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users.