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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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' |
5 Comments. Leave new
Why SA?
The SA account won’t quit, get fired, or get promoted out of being a DBA. Other accounts can be disabled. Using the SA account as the job owner means that the jobs will always run. If you’re worried about security, you can execute the individual job steps as a different user.
Is it common or best practice to set the job owner to SA?
I currently create service accounts (domain users) as job owners, granted with the appropriate permissions to execute the job steps. Is there any harm in this approach?
Note that if you are running SQL Server Reporting Services, whenever your users create a subscription to a report, a new Agent job is created. The job owner is set to the SSRS service account. (At least, this is true for SSRS 2008 R2).
So, don’t delete those jobs or your VP won’t get his daily management report!
These things are annoying b/c they are named with a GUID and take up space in the list of real jobs…
Great script, Brent, thanks!
We’ve come across this problem many times at my workplace: A SQL maintenance plan is created, and this in turn creates a bunch of SQL jobs. The maintenance plan is owned by the user who created the maintenance plan. They then go and modify the ownership of the jobs to our standard job-running account. Some time later, the maintenance plan is modified, overwriting the ownership of its jobs with whichever login owns the maintenance plan. This can stop the jobs from running.
For us, the fix is to change the maintenance plan ownership at the same time that we modify the job ownership:
(SQL 2008):
UPDATE msdb.dbo.sysssispackages
SET [ownersid] =suser_sid(‘name_of_job_login’)
WHERE name=’Name of maintenance plan’
(SQL 2005):
UPDATE [msdb].[dbo].[sysdtspackages90]
SET [ownersid] = suser_sid(‘name_of_job_login’)
WHERE [name] = ‘Name of maintenance plan’