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 USE MASTER GO --Drop and create the demo credential if it is already existing IF EXISTS (SELECT * FROM sys.credentials WHERE name = N'ProxyDemoCredentials') BEGIN DROP CREDENTIAL [ProxyDemoCredentials] END GO CREATE CREDENTIAL [ProxyDemoCredentials] WITH IDENTITY = N'YourDomain\ProxyDemoServiceAccount', SECRET = N'ServiceAccountPasswordHere' GO --End of Step 1 --Step 2 - Create a demo proxy account USE msdb GO --Drop the demo proxy if it is already existing IF EXISTS (SELECT * FROM msdb.dbo.sysproxies WHERE name = N'ProxyDemo') BEGIN EXEC dbo.sp_delete_proxy @proxy_name = N'ProxyDemo' END GO --Creating a demo proxy and assigning the demo credential to the same EXEC msdb.dbo.sp_add_proxy @proxy_name = N'ProxyDemo', @credential_name=N'ProxyDemoCredentials', @enabled=1 GO --You Need to Enable a Proxy before using it which can be done using below step. EXEC msdb.dbo.sp_update_proxy @proxy_name = N'ProxyDemo', @enabled = 1 GO --End of Step 2 --Step 3 Granting created demo proxy to SQL Agent subsystem EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'ProxyDemo', @subsystem_id=11 --subsystem 11 is for SSIS. You can get info using EXEC sp_enum_sqlagent_subsystems GO --End of Step 3 --Step 4 Granting access of security principals(service account) to demo proxy created USE msdb GO --Grant proxy account access to security principals EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'ProxyDemo' ,@login_name=N'YourDomain\ProxyDemoServiceAccount' --,@fixed_server_role=N'<fixed_server_role name>' if any roles --,@msdb_role=N'<msdb_role name>' if any MSDB roles GO --End of Step 4.
Now just use this proxy under Run AS in job step for using SSIS package.