Edit

Share via

sp_help_proxy (Transact-SQL)

  • Article

Applies to: SQL Server

Lists information for one or more proxies.

Transact-SQL syntax conventions

Syntax

sp_help_proxy
    [ [ @proxy_id = ] proxy_id ]
    [ , [ @proxy_name = ] N'proxy_name' ]
    [ , [ @subsystem_name = ] N'subsystem_name' ]
    [ , [ @name = ] N'name' ]
[ ; ]

Arguments

[ @proxy_id = ] proxy_id

The proxy identification number of the proxy to list information for. @proxy_id is int, with a default of NULL. Either the @proxy_id or the @proxy_name might be specified.

[ @proxy_name = ] N'proxy_name'

The name of the proxy to list information for. @proxy_name is sysname, with a default of NULL. Either the @proxy_id or the @proxy_name might be specified.

[ @subsystem_name = ] N'subsystem_name'

The name of the subsystem to list proxies for. @subsystem_name is sysname, with a default of NULL. When @subsystem_name is specified, @name must also be specified.

The following table lists the values for each subsystem.

Value Description
ActiveScripting ActiveX script
CmdExec Operating System (CmdExec)
Snapshot Replication Snapshot Agent
LogReader Replication Log Reader Agent
Distribution Replication Distribution Agent
Merge Replication Merge Agent
QueueReader Replication Queue Reader Agent
ANALYSISQUERY Analysis Services command
ANALYSISCOMMAND Analysis Services query
Dts SSIS package execution
PowerShell PowerShell script

[ @name = ] N'name'

The name of a SQL Server login to list proxies for. @name is nvarchar(256), with a default of NULL. When @name is specified, @subsystem_name must also be specified.

Return code values

0 (success) or 1 (failure).

Result set

Column name Data type Description
proxy_id int Proxy identification number.
name sysname The name of the proxy.
credential_identity sysname The Microsoft Windows domain name and user name for the credential associated with the proxy.
enabled tinyint Whether this proxy is enabled. 0 = not enabled, 1 = enabled.
description nvarchar(1024) The description for this proxy.
user_sid varbinary(85) The Windows security id of the Windows user for this proxy.
credential_id int The identifier for the credential associated with this proxy.
credential_identity_exists int Whether the credential_identity exists. 0 = doesn't exist, 1 = exists.

Remarks

When no parameters are provided, sp_help_proxy lists information for all proxies in the instance.

To determine which proxies a login can use for a given subsystem, specify @name and @subsystem_name. When these arguments are provided, sp_help_proxy lists proxies that the login specified might access and that might be used for the specified subsystem.

Permissions

This stored procedure is owned by the db_owner role. You can grant EXECUTE permissions for any user, but these permissions may be overridden during a SQL Server upgrade.

Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

The credential_identity and user_sid columns are only returned in the result set when members of sysadmin execute this stored procedure.

Examples

A. List information for all proxies

The following example lists information for all proxies in the instance.

USE msdb;
GO

EXEC dbo.sp_help_proxy;
GO

B. List information for a specific proxy

The following example lists information for the proxy named Catalog application proxy.

USE msdb;
GO

EXEC dbo.sp_help_proxy
    @proxy_name = N'Catalog application proxy';
GO

Related content