Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk . Microsoft Data Platform MVP

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein

Tuesday 23 June 2009

Change Ownership of a Maintenance Plan

To change the ownership of a Maintenance Plan (dts or SSIS package) the following scripts will make te changes

--SQL 2005 version
UPDATE msdb.dbo.sysdtspackages90
SET ownersid = 0x01 --this is the sa user but set to which ever user you want
WHERE name = 'MaintenancePlanName'

If this maintenance plan is scheduled the job owner could also need updating as well.

-- SQL 2005 version job owner update
USE msdb
EXEC msdb.dbo.sp_update_job @job_name=N'Your Job Name',

Examples for other versions of sql server are

--SQL 2000 version
UPDATE msdb.dbo.sysdtspackages
SET owner = 'sa',
owner_sid = 0x01 --sa user
WHERE name = 'MaintenancePlanName'

--SQL 2008 version

UPDATE msdb.dbo.sysssispackages
SET ownersid=0x01 --sa user
WHERE name='MaintenancePlanName'

No comments:

Post a Comment

Note: only a member of this blog may post a comment.