Job Owner,SQL Agent Account, Proxy accounts

For each SQL job that is created, there is a SQL/Windows account assigned as job owner.
The SQL Agent Service runs using a Windows account which is called the agent account.
It is interesting to see the account that is used to execute the jobs under different scenarios.


* When the job owner is a sysadmin account then the job runs using the SQL Agent account.

A small example is shown below. A sample job test is created. The job owner is set to SA.The TSQL step prints the current user name using the function SYSTEM_user.


Job owner is set to SA (Ref pic above ).


The step details are shown above.



As you can see that the job has been executed under Local System account which is my SQL agent account.

Just in case, if you dont want to execute under the agent account but on a different account then you can use the option run the job as option under advanced tab of each step.

But note that 'Run as ' option works only when the following are true.

* When the job owner is SA.
* When the Step type is T-SQL.

* When the job owner is a Non System Admin account, the SQL Job runs under the Job owner account.

Now I have changed the job owner to a Non Sysadmin account called 'Test'.
Note that 'Test' is ordinary SQL account.


By executing the job you can see from the image below that the job runs under context of the account 'Test'.


Now let me add another step which executes a simple operating system command.



Now the execution fails with the error 'Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed.'



As you may understand clearly from the error message, When the job owner is Non Sysadmin one doesnt have the right to execute operating system commands.

This can be a sort of drawback in certain cases where one is not interested in giving full rights to all jobs. In such a scenario, SQL Server gives an option to create a proxy account and execute the job using SQL Agent proxy account ie., When a Non Sysadmin owned job executes 'Operating system command' step or
'Activex Script step' then it runs under the context of Proxy account. Note that the Proxy account should be a Sysadmin account on the SQL Server. Another requirement of proxy accounts are SQL Server's service account should be a part of Windows Admin group while using proxy account.More details on Proxy account can be found here.

Just a few steps on how to enable proxy account are shown below.
Under SQLAgent properties -> Job system tab ->Uncheck SQL Agent
Picture provided below.



Key in your User id / password and Domain details for your Proxy account.
The proxy acct I have configured is proxy_acct.



Now let us execute the job again to see the results.



The job executed successfully. Notice that the job ran under the context of proxy_acct.



Also, note that the T-SQL step still executed under the context of
'Test' account. The Operating system command executed under the Proxy account.

The proxy account creation steps explained here are applicable for SQL 2k.
For 2k5 refer here