Who owns my job and who runs it?

There has usually been confusion over who owns the SQL Agent job and who runs executes the steps. Let’s look at combination of scenario where we try to set-up a package as SQL Agent job.

Scenario A: Job owner is a login which is a sys-admin (could be a domain account or SQL). Now, the job would always be run either as SQL Agent account or Proxy depending upon what option you have for “Run As” in that Agent Job step.

For example I have an SSIS job whose owner is sysadmin SQL Account ‘sa’ and has only one step with type “SQL Server integration Services Package” and “Run as SQL Server Agent Service Account” . I have my SQL Server Agent running under the context of NetworkService.

If I right click on my Job and do “Start Job at Step..” it runs successfully. Now, if I check the Job history I could see that job ran under the context of SQL Agent account i.e. network service.

Message

Executed as user: Domain\Boxname$. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 06:45:57 DTExec: The package execution returned DTSER_SUCCESS (0). Started: 06:45:57 Finished: 06:45:57 Elapsed: 0.062 seconds. The package executed successfully. The step succeeded.

If I setup my domain account Domain\MyUser as SQL Agent proxy name called Proxy1 for SQL Server Integration SubSystem and run the same SQL Agent Job’s step 1 as proxy Proxy1. This is how Job history looks

Message

Executed as user: Domain\MyUser. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 06:56:26 DTExec: The package execution returned DTSER_SUCCESS (0). Started: 06:56:26 Finished: 06:56:26 Elapsed: 0.031 seconds. The package executed successfully. The step succeeded.

The job ran under the context on proxy account.

Scenario B: Job owner is a login which is a non sys-admin (could be a domain account or SQL). Now, its becomes mandatory to setup steps in this job using a proxy account.

For example I have an SSIS job whose owner is non sysadmin SQL Account say ‘test’ and has only one step with type “SQL Server integration Services Package” and “Run as SQL Server Agent Service Account” .

If I try to run this job by right click the job and selecting “Start Job at Step..” This is the error message I get.

Message

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.

So, it becomes mandatory to setup your Job’s steps using a proxy account. Let’s try to change my Job’s step and make it run using proxy Proxy1 I created earlier. This is the error message I got this time.

Message

Unable to start execution of step 1 (reason: JobOwner test doesn't have permissions to use proxy 2 for subsystem SSIS). The step failed.

To resolve above error, I had to run following commands from query window. This mapped my proxy Proxy1 with login test.

USE msdb ;

GO

EXEC dbo.sp_grant_login_to_proxy

    @login_name = N'test',

    @proxy_name = N'Proxy1' ;

GO

If I right click on my Job and do “Start Job at Step..” it runs successfully. If I check the Job history I could see that job ran under proxy Domain\MyUser.

Message

Executed as user: Domain\MyUser. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 07:05:49 DTExec: The package execution returned DTSER_SUCCESS (0). Started: 07:05:49 Finished: 07:05:49 Elapsed: 0.047 seconds. The package executed successfully. The step succeeded.

Author : Runeet Vashisht , SQL Developer Engineer , Microsoft

Reviewed by : Malcolm Stewart , SQL Escalation Services , Microsoft