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

"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.