fredagen den 15:e januari 2010

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'

Regards
Stefan

0 kommentarer: