Job Owner Reverts to Previous Owner when Scheduled Maintenance Plan is Edited

Recently I inherited a Database from a retired DBA and after a while the maintenance jobs started to fail. When i excamined the log I got the following error:

The job failed. Unable to determine if the owner (domain\username) of job Log Backup Databasename.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user ‘domain\username’, error code 0x2. [SQLSTATE 42000] (Error 15404)).

Ok fine with that because I knew the account was disabled. I think no problem so I change the owner of the job, but no no. I ran the job with success after changing the owner of the job to sa, but when the schedule ran the job later at night it failed!

Why did it fail again? After examining the log I saw the error above was there again, how could that be I changed it the day before?

After some thinking it came back to me, after running the job successfully I edited the job and made a modification. When I saved the job it reverted back to the owner who created it from start…..

To fix the problem use the script below on sql 2005 if you set the dbo as owner you won´t have any problems with DBA´s leaving the company.

/*Here’s how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
–to find the name and owner of the maintenance plan
select * from msdb.dbo.sysdtspackages90
–to find the sid you want to use for the new owner
select * from sysusers

/*To update the owner to dbo use the script below
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = ‘MaintenancePlan’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: