SQL Server Agent: Job Owner does not exist anymore

A few days ago I‘ve got a message from one of my colleagues at work. He noticed during the routine backup checks that some of our SQL Server backup jobs were not working as they should.

Our SQL Server was throwing an error message for these job that reads:

The job failed. Unable to determine if the owner (<user name>) of job <job name> has server access (reason: Could not obtain information about Windows NT group/user '<user name>', error code 0x534. [SQLSTATE 42000] (Error 15404).

I quickly recognized the user name in the error message: It belongs to a colleague in our team who has recently left the company. We‘ve locked and deleted his user accounts in the Active Directory not that long ago.

And that was exactly the problem. Each Agent Jobs needs to have one user account – in SQL Server speak a Login – to be its owner. This owner is automatically set to the user account that is used to create the job.

However, this owner account doesn‘t really impact day-to-day execution or administration of this job. The job is always run as the service account of the SQL Server Agent service – regardless of the owner of the job. If you want your job to run under a different account, you‘ll need to use the „Run as“ feature and Proxies in SQL Server.

The ower account does have an impact on the permissions for the job, namely who has permissions to edit the job and run it. However in our setup we do not rely on this and always assign permissions to the jobs using server roles or SQL Server Agent fixed database roles.¹

What we didn‘t think about: The account you set as owner does have to exist – even if it isn‘t really used for anything.

So which jobs are affected by this? You can query the msdb database to find information on the jobs. Using the T-SQL function SUSER_SNAME can be used to convert the SID from the database to a human readable user name.

You can check your jobs using the query. You can even run this query on multiple SQL Server when you use Registered Servers in SQL Server Management Studio, which allows you to run a query on multiple machines at the same time.

SELECT 
    name,
    enabled,
    description,
    SUSER_SNAME(owner_sid) AS 'owner_name',
    owner_sid 
FROM msdb.dbo.sysjobs
WHERE SUSER_SNAME(owner_sid) LIKE '%<user name>%'

Now you have found all the jobs. And next? You‘ll need to change every single job and set another job owner (e.g. sa).

Actually, there is already a Stored Procedure for that. You can change the owner from one owner to another for all jobs at once using sp_manage_jobs_by_login:

EXEC dbo.sp_manage_jobs_by_login  
    @action = N'REASSIGN',  
    @current_owner_login_name = N'<old owner user name>',  
    @new_owner_login_name = N'<new owner user name>' ;  

This will take all jobs that have the old owner and change the owner for these jobs. Thanks for that, Microsoft – unironically.