1

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  
2
  • look at this answer to find the job that is using the proxy. Aug 30, 2017 at 17:43
  • I tried the following delete from sysproxies where proxy_id in (select proxy_id from sysproxies where name = 'CoreRefData' ) and saw my script ran without error. But cant see the jobstep when I run the entire script above
    – Tom
    Aug 30, 2017 at 17:55

2 Answers 2

3

Reference:

https://docs.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.

7
  • What i meant is dont you have to delete the record that matches the proxy_id
    – Tom
    Aug 30, 2017 at 18:11
  • Are you saying you need to update the proxy_id and not delete
    – Tom
    Aug 30, 2017 at 18:24
  • Yes but before that you need make sure none of your job step is using the proxy. In your case some job step is using it. Aug 30, 2017 at 18:25
  • So after I check and run this delete from sysproxies where proxy_id in (select proxy_id from sysproxies where name = 'CoreRefData' ), I see no jobstep is associated with the proxy. Is that correct
    – Tom
    Aug 30, 2017 at 18:33
  • Why do I need to run on different account or proxy if just the delete is doing the work for me
    – Tom
    Aug 30, 2017 at 18:34
1

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.

enter image description here enter image description here

Not the answer you're looking for? Browse other questions tagged or ask your own question.