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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s