SQL Agent Proxy Accounts

In SQL 2000, you could use a single windows account to be the proxy for all jobs that needed to run Sysadmin level tasks. New in SQL 2005 is that you can define multiple proxies for the non-Sysadmin.

To create:

1. Create or use a Windows login (local or domain) to be the proxy that will run the job.
2. Give that account "Log on as a batch job" right in the OS
3. Create SQL login for Windows login and give it SQLAgentUserRole in msdb.
4. Create the credential using SQL 2005 Management Studio (SSMS) to reference this account.
5. Create Proxy using SSMS and give it access to desired subsystem(s): ActiveX Script, Operating System, Replication Distributor, Replication Merge, Replication Queue Reader, Replication Snapshot, Replication Transaction-Log Reader, Analysis Services Command, Analysis Services Query, SSIS Package Execution
6. Job owner--->Proxy--->Credential--->Windows Login--->runs the job.

Reference: http://msdn.microsoft.com/en-us/library/ms189064(SQL.90).aspx