using SMO.Agent to retrieve Sql Job execution status - security issue

So ive got a C# program that fires off Sql Agent jobs using the 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.");
}

Im using Sql Server Auth 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 doesnt 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 sysadmin account - if thats the issue im not sure how to work around it.

Any ideas?

ansaurus

tags:

views:

answers:

using SMO.Agent to retrieve Sql Job execution status - security issue

related questions