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.

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

3 Responses to "How to Create a SQL Server Agent Proxy Account"
  1. martha said...
    August 4, 2009 at 6:41 AM

    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.


    Susan

    http://8080proxy.com

  2. Suprotim Agarwal said...
    August 7, 2009 at 3:00 AM

    Thanks Martha!

  3. MohanDeval said...
    May 26, 2011 at 11:52 AM

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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions