When we going to execute a SQL Agent job, most of the times we do not bother about execution user. By default SQL Agent job is executing under the SQL Agent service account. Most probably this account can be a high privileged user. (this is the account which is Log on account for SQL Agent service)
http://technet.microsoft.com/en-us/library/ms345578.aspx
Which means SQL Agent job is executing with administrative privilages. That is not a safe way because some average user can execute dangerous operating system commands and etc. So we need a safe way to execute SQL Agent job.
The solution is Proxy account!
Basically this is act as a proxy for a perticular user. In order to use proxy account we need to store set of credentials. If we store some average user’s credentials for this, then SQL Agent job is executing with those privilages. That’s not harm at all.
This is the way to use proxy account for executing a SQL Agent job.
1.Create the credential – Use an average user for credentials. (I used an average user named TestUser)
2.Create a proxy that references the credential
3. Assign the Principle for the proxy. ( TestUser is in SQLAgentUser role , so we need to add this role as a principle)
You can add user to the SQLAgentUser role as follows. (SQLAgentUser role is the lowest permission group to execute a SQL Agent job).
4.Assign the run as value in SQL Agent job step to the proxy.
Now execute the SQL Agent job and see the history. You can clearly see job is executed by that average user we mentioned in credentials.
