Search code examples
sqlsql-serversql-server-2016sql-agent

SQL Server - Enable Proxy Without Dropping And Recreating It

When I run the following proc:

SELECT * FROM msdb.dbo.sysproxies

I notice that one of my proxy accounts is not enabled (related to this). I'd like to create a script to enabled all disabled proxy accounts, but my understanding is that you need to drop the account and recreate it to re-enable it. In the process of doing this you need to know the linked credentials, description, and linked subsystem.

Is there a way to do this without dropping the proxy account, maybe some sort of simple update statement?

Solution

  • You could use sp_update_proxy stored procedure to enable proxy.

    sp_update_proxy

    Changes the properties of an existing proxy.

    USE msdb ;  
GO  

EXEC dbo.sp_update_proxy  
    @proxy_name = 'Catalog application proxy',  
    @enabled = 1;  
GO