sp_add_proxy (Transact-SQL)

  • Article

Adds the specified Microsoft SQL Server Agent proxy.

Syntax

sp_add_proxy
    [ @proxy_name = ] 'proxy_name' ,
    [ @enabled = ] is_enabled ,
    [ @description = ] 'description' ,
    [ @credential_name = ] 'credential_name' ,
    [ @credential_id = ] credential_id ,
    [ @proxy_id = ] id OUTPUT

Arguments

  • [ @proxy_name= ] 'proxy_name'
    The name of the proxy to create. The proxy_name is sysname, with a default of NULL. When the proxy_name is NULL or an empty string, the name of the proxy defaults to the user_name supplied.
  • [ @enabled = ] is_enabled
    Specifies whether the proxy is enabled. The is_enabled flag is tinyint, with a default of 1. When is_enabled is 0, the proxy is not enabled, and cannot be used by a job step.
  • [ @description= ] 'description'
    A description of the proxy. The description is nvarchar(512), with a default of NULL. The description allows you to document the proxy, but is not otherwise used by SQL Server Agent. Therefore, this argument is optional.
  • [ @credential_name = ] 'credential_name'
    The name of the credential for the proxy. The credential_name is sysname, with a default of NULL. Either credential_name or credential_id must be specified.
  • [ @credential_id = ] credential_id
    The identification number of the credential for the proxy. The credential_id is int, with a default of NULL. Either credential_name or credential_id must be specified.
  • [ @proxy_id= ] id OUTPUT
    The proxy identification number assigned to the proxy if created successfully.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

This stored procedure must be run in the msdb database.

A SQL Server Agent proxy manages security for job steps that involve subsystems other than the Transact-SQL subsystem. Each proxy corresponds to a security credential. A proxy may have access to any number of subsystems.

Permissions

Only members of the sysadmin fixed security role can execute this procedure.

Members of the sysadmin fixed security role can create job steps that use any proxy. Use the stored procedure sp_grant_login_to_proxy (Transact-SQL) to grant other logins access to the proxy.

Examples

A. Create a proxy

This example creates a proxy for the credential CatalogApplicationCredential. The code assumes that the credential already exists. For more information about credentials, see CREATE CREDENTIAL (Transact-SQL).

USE msdb ;
GO

EXEC dbo.sp_add_proxy
    @proxy_name = 'Catalog application proxy',
    @enabled = 1,
    @description = 'Maintenance tasks on catalog application.',
    @credential_name = 'CatalogApplicationCredential' ;
GO

See Also

Reference

CREATE CREDENTIAL (Transact-SQL)
sp_grant_login_to_proxy (Transact-SQL)
sp_revoke_login_from_proxy (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance