Blitz Result: Jobs Owned by User Accounts

SQL Server Agent jobs are like hot potatoes: they’re owned by whoever touched ’em last.  The job owner doesn’t really mean much – jobs can be owned by anyone and they’ll still work the same way.

Right up until the job owner’s account is dropped.

If the SQL Server was managed by someone who’s no longer with the company, all their jobs will suddenly stop working when SQL Server can’t verify the job owner’s account.  (This can also pop up if there’s an Active Directory problem when the job needs to run.)  The fix is to have the built-in SA account own all the jobs.

This part of our SQL Server sp_Blitz® script lists jobs owned by accounts other than SA.  If you’ve changed the name of the SA account, the jobs will show up here, but don’t pat yourself on the back just yet.  Changing the name of the SA account can break some SQL Server service packs, so make sure you understand the risks there.

What if the Job is Already Failing?

If the jobs have been failing because they’re owned by a user whose account has since been disabled, you probably don’t want to fix this problem right away.  The jobs may have been failing for months, and when you enable ’em again, you may run into serious side effects.  For example, we’ve seen cases where developers didn’t know why the job was failing, and they built a separate process to do the same work.  When the jobs were enabled again, the business processes broke.

You also want to understand the contents of any job before you enable it again.

TO FIX THE PROBLEM

If the job’s still running on a regular basis, it makes sense to change that job owner to SA to make sure it continues to run if/when the job owner’s account is disabled. Use the steps below to make your change:

Return to sp_Blitz® or Ask Us Questions

How to Change A SQL Agent Job’s Owner

When you’re ready to fix the problem, you can simply right-click on each job in SSMS and click Properties.  Change the owner to SA and click OK.

Want to do it even faster? Here’s a script from Luis Chiriff – run at your own risk:

select 
(case when D.is_read_only = 1 then '-- Remove ReadOnly State' when D.state_desc = 'ONLINE' then 'ALTER AUTHORIZATION on DATABASE::['+D.name+'] to [SA];' else '-- Turn On ' end) as CommandToRun
,D.name as Database_Name
, D.database_id the Database_ID
,L.Name as Login_Name
,D.state_desc as Current_State
,D.is_read_only as [ReadOnly]
from master.sys.databases D
inner join master.sys.syslogins L on D.owner_sid = L.sid
where L.Name <> 'sa'
order by D.Name

-- Agent Jobs

select 
J.name as SQL_Agent_Job_Name
,L.name as Job_Owner
,J.description 
,C.name
,'EXEC msdb.dbo.sp_update_job @job_id=N'''+cast(job_id as varchar(150))+''', @owner_login_name=N''sa'' ' as RunCode
from msdb.dbo.sysjobs j
inner join master.sys.syslogins L on J.owner_sid = L.sid
inner join msdb.dbo.syscategories C on C.category_id = J.category_id
where L.Name <> 'sa'