Info – SQL Server Proxy accounts and job agent execution contexts

SQL Server proxies are used in cases where impersonation is required in complex and secured environments and more control is required while executing packages, jobs, PowerShell commands etc. below links and details are collected to give more info on this topic, it requires a good amount of reading and may take up to 2-3 hours to understand it completely, so be patient if you land on this page and go through all links and articles to understand it better.

 

Links provided are in context of SQL Server 2008 R2…

 

1. SQL Server agent proxies and how to create

http://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx

 

a) How to: Create a Credential (SQL Server Management Studio)

http://technet.microsoft.com/en-us/library/ms190703(v=sql.105).aspx

 

b). How to: Modify a Proxy (SQL Server Management Studio)

http://msdn.microsoft.com/en-us/library/ms187890(v=sql.100).aspx

 

2. How to: Configure a User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio)

http://technet.microsoft.com/en-us/library/ms187901(v=sql.105).aspx

 

3. Configure a User to Create and Manage SQL Server Agent Jobs

http://technet.microsoft.com/en-us/library/ms187901.aspx

 

4. Implementing SQL Server Agent Security

http://msdn.microsoft.com/en-us/library/ms190926(v=sql.105).aspx

 

5. SQL Server Agent Fixed Database Roles

http://msdn.microsoft.com/en-us/library/ms188283(v=sql.105).aspx

 

6. The Run As drop-down list to select PROXY ACCOUNT in the General page does not apply to T-SQL scripts; it only applies to other step types that interact with the OS, such as PowerShell scripts, CmdExec, ActiveXScript, SSIS packages, etc. For T-SQL scripts you have to use EXECUTE AS to run it under different security context, more details are available on following links,

 

Ref. for this info:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6f507217-5594-40f5-9533-4a886f45871c/sql-job-run-as-dont-show-the-users-in-the-drop-down

Articles to read for EXECUTE AS information:

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/19/sql-server-agent-jobs-and-user-contexts.aspx

http://technet.microsoft.com/en-us/library/ms181362(v=sql.105).aspx

 

 

Other useful References:

 

SSIS and SQL Server Agent:

http://bi-polar23.blogspot.ca/2007/12/ssis-and-sql-server-agent.html

http://stackoverflow.com/questions/6712811/how-do-i-create-a-step-in-my-sql-server-agent-job-which-will-run-my-ssis-package

 

Details about Job creation with multiple options:

http://allcomputers.us/windows_server/sql-server-2008—sql-server-agent-(part-2)—jobs—proxies.aspx

 

http://blogs.msdn.com/b/varun_sharma/archive/2007/08/30/how-to-run-sql-server-agent-and-sql-server-jobs-with-least-privilege-in-sql-server-2005.aspx