Monday, May 04, 2009

How to change the owner of a maintenance plan

Scenario 1:
-------------
One of our members of staff had left and we had the usual case of a few jobs failing with:

"Unable to determine if the owner (DOMAIN\xxx) of job has server access (reason: error code 0x534. [SQLSTATE 42000] (Error 15404))."


Scenario 2:
------------
A job for running a nightly transaction log maintenance plan was created and owned by an account that was a member of the Domain Admins group. The account was subsequently removed from the Domain Admins group and the job failed (owner did not have server access), as expected.

The job owner was changed to another account that is a member of the Domain Admins group, and the job ran successfully for seven days. On the eighth day, another user, also a member of Domain Admins group, edited the maintenance plan to add a database. After the maintenance plan was saved, job ownership reverted to the original job creator (no longer a Domain Admin) and the job failed on its next scheduled run.

/*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

UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'

For SQL Server 2008:

update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] = 'MaintenancePlan'

No comments: