A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step.
---->>proxy login SELECT J.job_id , J.name , S.step_name , S.step_id , P.name AS ProxyName , SP.name AS CredentialUserName , SP.type_desc AS CredentialUserType FROM msdb.dbo.sysjobs J INNER JOIN msdb.dbo.sysjobsteps S ON S.job_id = J.job_id LEFT OUTER JOIN msdb.dbo.sysproxies P ON P.proxy_id = S.proxy_id LEFT OUTER JOIN sys.server_principals SP ON SP.sid = P.user_sid
How to create a SQL Server Agent proxy in SQL Server
-- creates credential CatalogApplicationCredential USE msdb ; GO CREATE CREDENTIAL CatalogApplicationCredential WITH IDENTITY = 'REDMOND/TestUser', SECRET = 'G3$1o)lkJ8HNd!'; GO -- creates proxy "Catalog application proxy" and assigns -- the credential 'CatalogApplicationCredential' to it. EXEC dbo.sp_add_proxy @proxy_name = 'Catalog application proxy', @enabled = 1, @description = 'Maintenance tasks on catalog application.', @credential_name = 'CatalogApplicationCredential' ; GO -- grants the proxy "Catalog application proxy" access to -- the ActiveX Scripting subsystem. EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name = N'Catalog application proxy', @subsystem_id = 2 ; GO
Generally used in Agent jobs