Let's talk about jobs - SQL Server Jobs (the kind that run your backups). Let us also discuss ownership. Ever hear your boss tell you, "Jack, I want you take ownership on this"? Well, when we put SQL Server jobs and ownership in the same sentence, we definitely should consider NOT taking ownership. In fact, DBA's should get into the habit of making the ‘sa' or dedicated SQL Server account the owner of the job.

As you know, when you are logged into SQL Server, even with your Windows Authenticated or SQL Server login, any job or maintenance plan will be created under this context, and will default your login as the owner of the newly created SQL job. If you have been a DBA for long time and working with other DBAs, you should know now that this can cause a lot of administrative headaches, especially in large sql infrastructure environments.

Such administrative annoyances and anguish can occur when a DBA or sysadmin uses their individual account to create jobs and maintenance plans, and then suddenly leaves the company. Typically, the user's account is locked or disabled, and eventually deleted. Or, system administrators change your domain or modify system policies that affect your account. For the DBA left behind, he or she must immediately deal with failing jobs, backups and maintenance plans, because the account is no longer valid, and the only solution is to change the owner of the job. This scenario is unfortunately quite common.

Can you imagine the potential liability for multiple failed backups and jobs, as well as the tedious task ahead of you to change, possible tens of dozens of such jobs and plans across multiple servers in your environment? Well, it does happen, and if this happens to you, then read on. This article aims to assist you in this regard, and provide you with some knowledge, scripts, and even the ability to enforce ownership in the future. Hopefully, if you are faced with this situation, the below tips can ease your pain, and save much of your valuable time.

Although you can substitute 'sa' for any valid login account, we will assume you want to make all jobs owned by 'sa', which in my opinion is the safe bet, as this sql account will never expire or be removed.

First, to change the owner of a job, which is relatively simple, you can run the following scripts against the msdb database. You can take these scripts and create some sort of batch process for multiple servers.

Example 1:, you can use the below syntax to reassign all jobs from the current job owner to 'sa' using the sp_manage_jobs_by_login system stored procedure.

USE msdb
EXEC sp_manage_jobs_by_login 'REASSIGN', 'Domain\User', 'sa'
go

Example 2: The next script will generate a sp_update_job statement for every job on the server, stored in the sysjobs table in the msdb database. The sp_update_job can be used to change the attributes of a job, including ownership. (To see a full list of parameters, click on the highlighted link). So, run the script, and then take the generated output and run it back in query analyzer. To get the right format, set 'Results to Text', this will properly place the 'go' separator.

select 'msdb..sp_update_job @job_name = ' + ''''+ name + ''','
+ ' @owner_login_name = ''sa''' + char(13) +  'go '
from msdb..sysjobs

The output will look something like this:

msdb..sp_update_job @job_name = 'myBackupJob1', @owner_login_name = 'sa'
go
msdb..sp_update_job @job_name = 'myBackupJob2', @owner_login_name = 'sa'
go

To learn some more things to consider on changing job ownership, you can check out this one scenario from MSSQLTips on SQL Server Agent Job Ownership.

So, seems simple enough, use the above scripts and we're done. Not so fast! One complication that comes into play that must be considered is your maintenance plans. If your maintenance plans (backups, index rebuilds, integrity checks, etc) were created and are running under a login account. You can not just change the jobs created by a maintenance plan to 'sa' or another functional domain account that you use to run the jobs with. You will need to change the Maintenance Plan owner as well. If you don't and a maintenance plan is change for whatever reason the ownership of all jobs reverts back to the owner of a maintenance plan. To correct this you can run the attached script with the correct Maintenance Plan name. Modify a subtask within the maintenance plan then SAVE it, then change it back & SAVE. The ownership of the maintenance plan will change & all subtask jobs listed in the maintenance plan will change to 'sa'. For this article, again we will be using 'sa' as our standard.

use msdb;
go
update dbo.sysssispackages
set ownersid = (
select sid from msdb.sys.syslogins where name = 'sa')
where [name] = 'MaintenancePlanNameHere';

Finally, once you've cleaned up all your jobs and maintenance plans, you may want to consider a way to enforce ownership to default to 'sa' going forward. For this, I have created a custom INSTEAD OF TRIGGER that can be placed on the sysjobs table in the msdb database. There should be no performance overhead caused by this trigger, since it is fired 'instead of' the insert that occurs when a new job is created. (sp_add_job). In other words, there is nothing to ROLLBACK, as it will substitute 'sa' as the owner_sid.

(Note: This will work on SQL Server 2005 and 2008)

CREATE TRIGGER tr_enforce_sa_job_owner on sysjobs INSTEAD OF INSERT as
 BEGIN
 INSERT INTO msdb.dbo.sysjobs
 (job_id,
originating_server_id,
 name,
 enabled,
 description,
start_step_id,
category_id,
owner_sid,
notify_level_eventlog,
notify_level_email,
notify_level_netsend,
notify_level_page,
notify_email_operator_id,
notify_netsend_operator_id,
notify_page_operator_id,
delete_level,
date_created,
date_modified,
version_number)
 select job_id,
originating_server_id,
 name,
 enabled,
 description,
start_step_id,
category_id,
 suser_sid('sa',0),--owner_sid is substituted with the 'sa' login here,
notify_level_eventlog,
notify_level_email,
notify_level_netsend,
notify_level_page,
notify_email_operator_id,
notify_netsend_operator_id,
notify_page_operator_id,
delete_level,
date_created,
date_modified,
version_number from inserted i
 END

In conclusion, I hope the above information is helpful and contains information that you can use 1) to correct such a scenario as described, and 2) prevent this from ever happening. So, next time you here the phrase: 'Take ownership', when it comes to sql server jobs, think again!

Pearl Knowledge Solutions, Inc., is pleased to announce that it is now offering quality DBA managed services and remote 24x7x365 monitoring from its network of skilled DBA experts! To inquire about our services, and obtain a FREE SQL Server report, contact us at sqlcentric@pearlknows.com.

Quality DBA Services from the folks who brought you:

When SQL is the Center of Your Universe!

SQLCentricnow supports SQL Server 2008!

Coming Soon! Our SQLCentric SQLOS live Performance Dashboard! Our dashboard will be available as a stand-alone solution, or fully integrated with SQLCentric. See all critical performance measures and vital stats from one web console in real-time! Feel free to contact us for more info.