------------------
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
where [name] = 'Maintence paln name'
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'
--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'
SQL Server 2000 Maintenance Plan
--change the owner of a SQL Server 2000 Maintenance Plan UPDATE [msdb].[dbo].[sysdbmaintplans] SET [owner] = 'sa' WHERE [plan_name] = 'YOUR_MAINT_PLAN'
Query for finding the Maintence Plan Owner
ReplyDeleteSELECT name as PackageName, suser_sname(ownersid) as Owner
FROM msdb..sysdtspackages90
ORDER BY name
Query for finding the Maintence Paln Owner in SQL Server 2008
ReplyDeleteselect name as Packagename,SUSER_sname(ownersid) as OWnername from msdb.dbo.sysssispackages