CODEWITHSUNDEEP
c#securityjobsstatussmo
dalyons
dalyons

Reputation: 1304

Using SMO.Agent to retrieve SQL job execution status - security issue

I've got a C# program that fires off SQL Server Agent jobs using the SQL Server Management Objects (SMO) interfaces. It looks something like:

Server ssis_server = new Server(
    new ServerConnection(SERVER_NAME, SERVER_USERNAME, SERVER_PASSWORD)
);

var agent = ssis_server.JobServer;
var ssis_job = agent.Jobs[job_name];

var current_status = ssis_job.CurrentRunStatus;

if (current_status == JobExecutionStatus.Idle)
{
    ssis_job.Start();
    OnSuccess("Job started: " + job_name);
}
else
{
    OnError("Job is already running or is not ready.");
}

I'm using SQL Server Authentication at this point to simplfy things whilst I work this out.

Now, my problem is that unless the SERVER_USERNAME is part of the 'sysadmin' dbo role, ssis_job.CurrentRunStatus is always 'Idle' - even when I know the job is running. It doesn't error out, just always reports idle.
If the user is an administrator, then the status is returned as expected.

Role membership you say?
Well, I added the SERVER_USERNAME SQL Server login to the msdb Role SQLAgentOperatorRole, that didn't seem to help.
The job's owner is a system administrator account - if that's the issue I'm not sure how to work around it.

Any ideas?

Upvotes: 6

Views: 4076

Answers (1)

steoleary
steoleary

Reputation: 9298

You need to refresh the job by calling the Refresh() method on ssis_job before checking the status, then you will get the correct information.

Upvotes: 7

Related Questions