July 26, 2009

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, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal


martha said...

I recently came accross your blog and have been reading along. I thought I would leave my first comment. I dont know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.



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