How to Create a SQL Server Agent Proxy Account

If a person creating a SQL Server Agent job does not have permissions to use the resources needed by the job, a SQL Server Agent Proxy can be created in this case that corresponds to a security credential that has the necessary permission. Let us see how to create a SQL Server Agent Proxy in SQL Server 2005/2008

The sp_add_proxy SQL Server Agent Stored Procedure adds the specified Microsoft SQL Server Agent proxy.

The first step is to create a Credential and then create a proxy for that credential.

CREATE CREDENTIAL SUPROAGA WITH IDENTITY = 'SUPROTIM',
    SECRET = 'hgfd54@-d45dOOk8';
GO

USE msdb
GO

EXEC dbo.sp_add_proxy
    @proxy_name = 'Some Proxy',
    @enabled = 1,
    @description = 'To Save Queries to a filesystem',
    @credential_name = 'SUPROAGA' ;
GO

As shown above, we first create a credential SUPROAGA with identity=SUPROTIM who is an existing Windows User on this machine. We then created a proxy for that credential using sp_add_proxy.

The last step could be grant the proxy appropriate permissions of the SQLAgentUserRole.

EXEC sp_grant_login_to_proxy
    @proxy_name=N'Some Proxy',
    @msdb_role=N'SQLAgentUserRole'
GO