How to create proxy in SQL Server?

Berore reading this post if you want to understand what a proxy is then you can go through the following post here

So now coming to creation of proxy there are basically two ways:

First method would be using the SSMS(SQL SERVER MANAGEMENT STUDIO) – Object Explorer
Second method would be using CREATE PROXY Query.

First I would explain how to create proxy using Create proxy query and later in the post I would show how to create proxy using SSMS Object Explorer.

Create Proxy using SQL QUERY:

Syntax for Creating proxy:
sp_add_proxy @proxy_name = <<name_of_the_proxy>> ,
@description = <<description_of_proxy>> ,
@credential_name = <<name_of_the_credential>> ,
@credential_id = <<credential_id>> ,
@enabled = <<1 or 0>> ,
@proxy_id = <<proxy_id >> OUTPUT

So in the steps to create proxy here first we need to create a credential.
Further information about creating credentials can be found here

First let us create a credential,
lets call it cred_jhon,
So let us suppose the username and passowrd be jhon and pass000

Query to create credential:
CREATE CREDENTIAL [cred_jhon] WITH IDENTITY = N’jhon’, SECRET = N’pass000′

Now that credential is created we can create an agent proxy now.

Query to create proxy:

declare @proxy_id int
exec sp_add_proxy @proxy_name = ‘proxy1’,
@description = ‘simple proxy’,
@credential_name = ‘cred_jhon’, –The credential that we created above.
@enabled = 1, –its enabled
@proxy_id = @proxy_id OUTPUT

SELECT @proxy_id

procedure sp_add_proxy has to be run on msdb database.
execute permissions on sp_add_proxy are given to the members of sysadmin role.

The above query creates a proxy using credential cred_jhon.
Now that the proxy is created but this will be un assigned as we did not map it to any subsystem.
More info about subsystems can be found here

To map this proxy to a subsystem the procedure sp_grant_proxy_to_subsystem has to be used.


EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’proxy1′, @subsystem_id=11


The above command maps this proxy to SSIS Subsystem.

Following are the subsystemid’s:

subsystem_id Sub System
2 Microsoft ActiveX Script
3 Operating System
4 Replication Snapshot Agent
5 Replication Log Reader Agent
6 Replication Distribution Agent
7 Replication Merge Agent
8 Replication Queue Reader Agent
9 Analysis Services Command
10 Analysis Services Query
11 SSIS package execution
12 PowerShell Script

Creating Proxy Using Object Explorer – SSMS:

Following are the steps to create proxy using SSMS:

Connect to SQL server in object explorer

Connect to sql server in object explorer
Connect to sql server in object explorer

In object explorer expand SQL Server Agent >> Proxies
Note: If SQL Server Agent is disabled then check if the agent service is started in services.msc

Expand SQL Server Agent and Proxies
Expand SQL Server Agent and Proxies

RightClick on Proxies and click on New Proxy

Right Click On Proxies And Click On New Proxy
Right Click On Proxies And Click On New Proxy

Now Enter the proxy name

Enter Proxy Name
Enter Proxy Name

Then cllick on the button beside “Credential Name” box (highlighted button in above image)

Then Select Credential window appears

Select Credential Window
Select Credential Window

Now click on Browse and select the credential that you want to use for proxy and click on OK

Browse for Credentials
Browse for Credentials

Information about creating credentials can be found here

Now click on Ok in Select Credential window
In the “Active to the following subsystems” tab select the subsystems for the proxy

Select Sub Systems
Select Sub System

Then click on OK to create the Proxy

In order to view the newly created proxy, expand the subsystem that is mapped to the Proxy.

expand the subsystem that is mapped to the Proxy
expand the subsystem that is mapped to the Proxy

Now you have created a proxy in sql server in two ways.

Hope this post helped you!!!!



Following are the different subsystems of SQL Server Proxies:

  • ActiveX Script
  • Operating System (cmdExec)
  • Replication Distributor
  • Replication Merge
  • Replication Queue Reader
  • Replication Snapshot
  • Replication Transaction-Log Reader
  • SQL Server Analysis Services Command
  • SQL Services Analysis Services Query
  • SQL Server Integration Services Package
  • Power Shell

Why so many subsystems?

SQL Server Proxies are mainly used to impersonate different users authentication for a job step.
So if we open the a job step we find the same subsystems shown above as types.

Below are the screen shots that explain in more detail.

New Proxy – Sub Categories:

New Proxy
New Proxy - Sub Categories

New Job Step – Types:

New Job Step
New Job Step - Types

Hence the Subcategories in Proxies are related to Job types in Jobs.



SQL Server Proxy is a named holder of SQL Server Credentials.
SQL Server Proxies are basically used to definte the security context of a job step.
These proxies provide the job with the security credentials that were defined by the proxy.
The sql server agent first imporsinates the security credentials of the proxy and then runs the job step.

For example if there are two sql servers, server A and the other server B.
There is a requirement where in one step in the agent job on server A has to access the data from one of the tables in server B.
Let us suppose X has read permissions on server B.
Now in order to give the Job step permission on server B the following steps are performed on server A:

  • New Credential for X is created (i.e Identity and password are saved)
  • New Proxy is created using the credential created above.
  • The proxy is mapped with the subsystem as required.(this depends on what the job step does)
  • The job step is modified to use this proxy.