Trigger and execute SQL Server Agent Job

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.Parameters.AddWithValue(“@job_name”, jobName);
execJob.Connection = dbConn;

try
{
using (dbConn)
{
dbConn.Open();
using (execJob)
{
execJob.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
exception = ex;
}

return exception;
}

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.