SQL Agent Job fails after upgrade to SQL 2017

Question

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?

Answer 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