Cannot delete proxy (7). It is used by at least one jobstep. Change this proxy for all jobsteps first

Question

I have written a script to delete and create a proxy but getting the following error

Cannot delete proxy (7). It is used by at least one jobstep. Change this proxy for all jobsteps first.

USE [msdb]
GO

-- Drop the proxy if it already exists

IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = 'CoreRefData')
BEGIN
  EXEC msdb.dbo.sp_delete_proxy @proxy_name = N'CoreRefData'
END
GO


EXEC msdb.dbo.sp_add_proxy @proxy_name=N'CoreRefData',@credential_name=N'CoreRefData', 
        @enabled=1
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'CoreRefData', @subsystem_id=11
GO


EXEC dbo.sp_grant_login_to_proxy  
    @login_name = N'INT\svc-w-corerefdata-de',  
    @proxy_name = N'CoreRefData' ;  
GO  

Answer 1

Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-proxy-transact-sql

Your problem is:

If a job step refers to the proxy specified, the proxy cannot be deleted and the stored procedure fails.

You should be able to use this query to get the proxy_id.

SELECT * FROM sysproxies WHERE NAME = 'CoreRefData' 

Then run this to see what jobs are using the same proxy. Change the proxy_id from 1 to whatever you get from above query. If this query does not return any record you are good to delete the proxy but if it does records your delete will fail as explained in the above Microsoft document.

USE msdb; 
GO
SELECT sysjobsteps.job_id, 
       sysjobs.NAME    AS 'JobName', 
       sysjobsteps.step_id, 
       sysjobsteps.step_name, 
       sysjobsteps.subsystem, 
       sysjobsteps.last_run_date, 
       sysjobsteps.proxy_id 
       --, sysjobsteps.step_uid 
       , 
       sysproxies.NAME AS 'ProxyName' 
FROM   sysjobsteps 
       LEFT JOIN dbo.sysproxies 
              ON sysjobsteps.proxy_id = sysproxies.proxy_id 
       LEFT JOIN dbo.sysjobs 
              ON sysjobsteps.job_id = sysjobs.job_id 
WHERE  sysjobsteps.proxy_id = 1 

You can do few things here (the goal is to disassociate the proxy with any Job step):

1. Change those Job steps to run under different account temporarily. It can be SQL Agent account for example.
2. Script out those job steps and delete temporarily.

Once done you should be able to run your script without any error.

Answer 2

In my case to resolve the message...

“Cannot delete proxy (1). It is used by at least one jobstep”

... I had to change the properties for the “Allow to the following subsystems” and remove Powershell.