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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook