Muokkaa

Jaa

Create a SQL Server Agent proxy

  • Artikkeli

Applies to: SQL Server

This article describes how to create a SQL Server Agent proxy in SQL Server by using SQL Server Management Studio or Transact-SQL.

A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step.

On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server or SQL Agent job limitations in SQL Managed Instance for details.

Limitations

You must create a credential before you create a proxy if one isn't already available.

SQL Server Agent proxies use credentials to store information about Windows user accounts. The user specified in the credential must have "Access this computer from the network" permission (SeNetworkLogonRight) on the computer on which SQL Server is running.

SQL Server Agent checks subsystem access for a proxy and gives access to the proxy each time the job step runs. If the proxy no longer has access to the subsystem, the job step fails. Otherwise, SQL Server Agent impersonates the user that is specified in the proxy and runs the job step. For a list of proxy subsystems, see sp_grant_proxy_to_subsystem.

Creation of a proxy doesn't change the permissions for the user that is specified in the credential for the proxy. For example, you can create a proxy for a user that doesn't have permission to connect to an instance of SQL Server. In this case, job steps that use that proxy are unable to connect to SQL Server.

If the login for the user has access to the proxy, or the user belongs to any role with access to the proxy, the user can use the proxy in a job step.

Permissions

Only members of the sysadmin fixed server role have permission to create, modify, or delete proxy accounts. Users who aren't members of the sysadmin fixed server role must be added to one of the following SQL Server Agent fixed database roles in the msdb database to use proxies: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

Requires ALTER ANY CREDENTIAL permission if creating a credential in addition to the proxy.

Use SQL Server Management Studio (SSMS)

  1. In Object Explorer, select the plus sign to expand the server where you want to create a proxy on SQL Server Agent.

  2. Select the plus sign to expand SQL Server Agent.

  3. Right-click the Proxies folder and select New Proxy.

  4. On the New Proxy Account dialog box, on the General page, enter the name of the proxy account in the Proxy name box.

  5. In the Credential name box, enter the name of the security credential that the proxy account will use.

  6. In the Description box, enter a description for the proxy account

  7. Under Active to the following subsystems, select the appropriate subsystem or subsystems for this proxy.

  8. On the Principals page, add or remove logins or roles to grant or remove access to the proxy account.

  9. When finished, select OK.

Use Transact-SQL

The following script creates a credential called CatalogApplicationCredential, creates proxy Catalog application proxy and assigns the credential CatalogApplicationCredential to it, and grants the proxy access to the ActiveX Scripting subsystem.

  1. Create credential CatalogApplicationCredential.

    USE msdb;
GO

CREATE CREDENTIAL CatalogApplicationCredential
    WITH IDENTITY = 'REDMOND/TestUser', SECRET = 'G3$1o)lkJ8HNd!';
GO

  2. Create proxy Catalog application proxy and assign the credential CatalogApplicationCredential to it.

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

  3. Grant the proxy Catalog application proxy access to the ActiveX Scripting subsystem.

    EXECUTE dbo.sp_grant_proxy_to_subsystem
    @proxy_name = N'Catalog application proxy',
    @subsystem_id = 2;
GO

Related content