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.

SECRET = 'hgfd54@-d45dOOk8';

USE msdb

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

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',

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


Suprotim Agarwal said...

Thanks Martha!

MohanDeval said...

Ok. Creating proxy account is good, but how would you give to it rights to create ActiveX com object. , my SQL Job screams about, where as the DTs package runs smoothly. I have SQL2008 R2, Win2k8 R2, ?