0

I have upgraded a database to SQL 2017. A SQL Agent Job that previously ran now fails with this error:

Unable to start execution of step 1
(reason: JobOwner DOMAIN\USERNAME doesn't have permissions to use proxy 4 for subsystem PowerShell).
The step failed.

This job ran before the upgrade, so I'm confused why permissions would have changed. But no matter, I grant permissions with this script:

USE msdb
GO
EXEC dbo.sp_grant_login_to_proxy 
    @login_name = N'DOMAIN\USERNAME',
    @proxy_id = 6
GO 

 

Which then reminds me that the user in question is an admin, and can be neither granted nor denied anything:

'DOMAIN\USERNAME' is a member of sysadmin server role and cannot be granted to
or revoked from the proxy. Members of sysadmin server role are allowed to use any proxy.

So what am I missing? How can I fix this, or where should I look to determine what the underlying problem is?

5
  • 1
    Did you do an in-place upgrade or side by side and migration?
    – J.D.
    Aug 10, 2022 at 21:41
  • @J.D., this was an in-place upgrade Aug 11, 2022 at 3:15
  • Did the upgrade process ended with any error or warning? Is it any job that fails now or just that one? Have you tried altering that principal for another one to check if it is the culprit?
    – Ronaldo
    Aug 11, 2022 at 11:14
  • Can you narrow it down to the smallest job possible that still exhibits the error? See stackoverflow.com/help/minimal-reproducible-example for some information about how to work toward an example that makes it easy for others to help you.
    – Doug Deden
    Aug 11, 2022 at 19:46
  • Did Proxy #4 get disabled? Maybe the error message is misleading. Take a look at the results of select * from msdb.dbo.sysproxies.
    – Doug Deden
    Aug 11, 2022 at 19:47

1 Answer 1

1

I ended up creating the proxy under a new name, then reassigning all the jobs over to the new proxy.

Here are the steps I used:

  1. Right click on the existing proxy, and do script as>create.

  2. Modify the resulting creation script by changing the name to "BROKEN PROXY NAME - TEMP", and run it. At this point, I have the old broken proxy, and a new copy of it, with a TEMP suffix in the name.

  3. Use the script below to generate a script that will assign all jobs from the old proxy to the new one using the script below. Run the SELECT, copy the "Sql to change proxy" column, and run that series of EXEC statements.

  4. Delete the old proxy

  5. Rename the new proxy to have the old name

     declare @broken_proxy_name nvarchar(100) = 'BROKEN PROXY NAME'
     declare @temporary_proxy_name nvarchar(100) = @broken_proxy_name + ' - TEMP'
     SELECT
       sj.[name] as jobname
      ,sjs.proxy_id
      ,spx.[name] as "Proxy name"
      ,'EXEC msdb.dbo.sp_update_jobstep @job_id=N'' + convert(nvarchar(36),sj.job_id) + '', @step_id=' + convert(nvarchar(10),sjs.step_id) + ',@proxy_name=N''' + @temporary_proxy_name + '''--' + sj.[name] as "Sql to change proxy"
      ,'EXEC sp_start_job N'' + replace(sj.[name],'''','''''') + ''' as "Sql to run the job"
      ,sjs.*
     FROM
       msdb.dbo.sysjobs sj
       join msdb.dbo.sysjobsteps sjs on sjs.job_id = sj.job_id
       join msdb.dbo.sysproxies spx on spx.proxy_id = sjs.proxy_id
     where spx.[name] = @broken_proxy_name
       and sj.[enabled] = 1
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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