How to Create SQL Agent Proxy

Create the SQL Agent Job Proxy

Using SQL Server Management Studio Connect to the instance using Object Explorer, navigate to Credentials and select New Credential. A credential links the Proxy account to a Windows account with the required authorities



Set the credential name to be the same as the Windows account name that will be used, and then click on the ellipses button by the Identity field




Select the Location to the domain and select the Windows account name that will be used, and then click OK.



Type the password for the account, and then click OK



Navigate to Logins and select New Login



Locate the required proxy account login. Ensure that "Map to Credential" is checked, and select the credential created for the account. Click Add to complete the mapping



Set the Default database.



The next stage is to configure the job owner account so it can manage jobs. Select the User Mapping page. Enable access to msdb, and grant use of the SQLAgentUserRole. The SQLAgentUserRole authority is required both to allow the account to manage the jobs it owns, and to allow the associated credential to function as a SQL Agent proxy. Refer to BOL to decide if you also need to associate the proxy with the SQLAgentReaderRole or SQLAgentOperatorRole



Click on the ellipses in the Default Schema column. Type dbo, click on Check Names then click OK



Assign the database roles and default schema specified by the Application Support Team for this account. Then click OK to save the new login.




Associate Proxy with SQL Agent Subsystems

The next stage is to associate the proxy with one or more SQL Agent subsystems.

Navigate to SQL Server Agent -> Proxies, right-click on a required subsystem (e.g. Operating System (CmdExec)) and select New Proxy.



Type the name of the proxy, which should be the same as the Credential and account name, then click the ellipses button by the Credential Name field



Click Browse



Select the required credential name, and then click OK. Click OK again to return to the New Proxy Account window



The proxy must be associated with a SQL login to obtain the authorities it will use within SQL Server. (The proxy obtains the authorities it will use within Windows from the credential.) Select the Principals page and click Add



Set the Principal type to SQL Login, and select the login with the same name as the proxy. This ensures that any authorities granted to the account are also available to the proxy


Click OK until you return to SQL Server Management Studio.

The same procedure can be performed using the spCreateAgentProxy stored procedure.


Associate Proxy with Job Steps

The proxy must now be associated with the required job steps.
Either create a new SQL Agent job or edit an existing one. Set the job owner to match the proxy you are going to use.





Select the Steps page, highlight the step to be configured and click Edit.



Set the Run as field to the required proxy



Repeat this process for all other job steps that need to be run using this proxy. Click OK until the job is saved.
SQL Server Management Studio can now be closed:

Date Modified : June 11, 2010