SQL Server Agent Proxies are very helpful in executing some jobs. I had came across a situation where in I was scratching my head so as to how to accomplish a task which I wanted to run as SQL server Agent Job. First let me give some brief description about proxies.
SQL Server Agent uses proxies to manage security contexts. A proxy can be used in more than one job step. A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.
Now lemme give you a small example where how exactly a proxy can be helpful.
Say you have around 10 database servers, both SQL Engine and SQL Agent services on these servers run on different Service Account say Machine Specific. Ex Ser1, Ser2, Ser3 and so on. Now here is the task. You want some operations may be a T-SQL or some script combined into SSIS package to be run on all these services. One way to achieve this is schedule a Job on each of these server and what if you want a consolidated data like health of all servers or logins existing on these servers as single report. This is where your Proxy comes in handy. To achieve this task get a Service Account(A windows Domain user Account), give access to this service account on all servers and then create a credential for this service account and create proxy for this credential on any of the one server and execute this Job or package from one single server and get combined data on one server. By doing so what exactly happens is SQL server agent impersonates the associated User account with proxy against the server on which the script/job needs to be run and since you have provided access to this service account on all server you will be able to run the same. Now lemme guide you through a step wise procedure to create a proxy using T-SQL.
--Detailed Steps to create a SQL proxy using T-SQL
--Step 1 - Create a credential for proxy
--Drop and create the demo credential if it is already existing
IF EXISTS (SELECT * FROM sys.credentials WHERE name = N'ProxyDemoCredentials')
DROP CREDENTIAL [ProxyDemoCredentials]
CREATE CREDENTIAL [ProxyDemoCredentials]
WITH IDENTITY = N'YourDomain\ProxyDemoServiceAccount',
SECRET = N'ServiceAccountPasswordHere'
--End of Step 1
--Step 2 - Create a demo proxy account
--Drop the demo proxy if it is already existing
IF EXISTS (SELECT * FROM msdb.dbo.sysproxies WHERE name = N'ProxyDemo')
@proxy_name = N'ProxyDemo'
--Creating a demo proxy and assigning the demo credential to the same
@proxy_name = N'ProxyDemo',
--You Need to Enable a Proxy before using it which can be done using below step.
@proxy_name = N'ProxyDemo',
@enabled = 1
--End of Step 2
--Step 3 Granting created demo proxy to SQL Agent subsystem
@subsystem_id=11 --subsystem 11 is for SSIS. You can get info using EXEC sp_enum_sqlagent_subsystems
--End of Step 3
--Step 4 Granting access of security principals(service account) to demo proxy created
--Grant proxy account access to security principals
--,@fixed_server_role=N'' if any roles
--,@msdb_role=N'' if any MSDB roles
--End of Step 4.
Now just use this proxy under Run AS in job step for using SSIS package.