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:
The job failed. Unable to determine if the owner (Owner_Name) of the job Job_Name has server access.
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:
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:
Thanks for reading!