Missing SQL Server Agent Job Owner

Do you know which account is the owner for your SQL Server Agent jobs? Some jobs might be owned by user accounts which can become a problem. What happens if that job owner isn’t around forever and goes away? Will that job just keep chugging along?

Without an existing owner, a SQL Server Agent job will not run. Once a user gets disabled or removed from Active Directory, a job is still going to try running under that user but will begin failing. If you check job history for the reason, you find a message stating:

Message
The job failed. Unable to determine if the owner (Owner_Name) of the job Job_Name has server access.

Changing Ownership

The solution is simple enough. Update the job owner to that of an existing user. Even better, consider updating the job to use the SQL Server sa account since the sa account cannot be removed.

Right click on your job and select Properties:

We’ll change TestLogin:

To sa:

As usual, we can click the OK button to save changes or click the Script button and script out the changes to execute. This particular job would be:

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_id=N'6bf63f0d-99e6-41a1-a0af-200b96b201c2', 
		@owner_login_name=N'sa'
GO

Don’t Get Pwned

Now is a good time to check your jobs and owners to see if changes are needed. Run the following script to get your list:

SELECT sj.name AS Job_Name, sl.name AS Job_Owner
FROM msdb.dbo.sysjobs sj
LEFT JOIN master.sys.syslogins sl ON sj.owner_sid = sl.sid
ORDER by sl.name;

This will give you the job information needed to make corrections.

Even if you know your job owners are configured correctly, maybe now is a good time to review your server’s SQL Server Agent jobs. Do you have outdated jobs that should be updated or can be removed? Are the jobs that are completing successfully really completing successfully? Watch out for the dirty trick of job steps that report success even on failure:

aka…

Thanks for reading!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s