Jobs Owned by User Accounts
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 and Eric Eyster – run at your own risk:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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 as 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 --Agent Jobs select J.name as SQL_Agent_Job_Name ,msdb.dbo.SQLAGENT_SUSER_SNAME(j.owner_sid) 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 msdb.dbo.SQLAGENT_SUSER_SNAME(j.owner_sid) <> 'sa'; |
AG and Mirroring Endpoints Owned by Users
You may also end up here at this page if we found endpoints owned by user accounts rather than the SA account. In that case, read:
- Database mirroring breaks when the endpoint owner’s account is deleted
- Problems dropping logins involved with AG endpoints
To fix that, change the endpoint’s owner to SA, and then make sure to grant permissions to each replica’s service account for all of the other replicas’ endpoints.