-- Change_SQL_Maint_Plan_Owner.sql
/*

This script will update the owner of all maintenance plans on a
SQL Server 2005, 2008, or 2008 R2 server to the owner entered in variable @new_owner.

This will ensure that the maintenance plan jobs will all run under a valid login,
and not revert back to the login that created the maintenance plan.

The owner must be a login that is a sysadmin, is not a Windows group,
has not been denied access, and has access.

After updating the maintenance plan owner, it is necessary to do
an update to the plan to cause the change to take effect in the plan jobs.
Opening the plan in SQL Server management Studio, and saving it is enough.

*/

declare @new_owner	sysname
set @new_owner	= N'sa'

if	not exists (
	select name from sys.syslogins
	where	sid = suser_sid(@new_owner) and 
		denylogin <> 1 	and 
		hasaccess = 1 	and 
		sysadmin = 1 	and 
		isntgroup <> 1 )
	begin
	print 'Invalid login for Maintenance Package owner, '+isnull(@new_owner,'NULL')
	goto Script_Exit
	end


if	-- Verify SQL Server version is 2005
	left(convert(varchar(50),serverproperty ('ProductVersion')),2) = '9.'
	begin

	print 'Changing SQL 2005 Maintanance Plan Owners'

	print 'Before Update' 
	select  [Plan Owner.Plan Name]	=
		rtrim(quotename(rtrim(isnull(b.name,'NULL')))+'.'+quotename(rtrim(a.name)))
	from
		msdb.dbo.sysdtspackages90 a
		left join
		sys.syslogins b
		on a.ownersid = b.sid
	where
		a. packagetype = 6	-- 6 = Maint Plan

	print 'Update plan owner to '+isnull(@new_owner,'NULL')
	update	msdb.dbo.sysdtspackages90 
	set 
		ownersid = suser_sid(@new_owner)
	where
		ownersid <> suser_sid(@new_owner)	and
		suser_sid(@new_owner) is not null	and
		packagetype = 6		-- 6 = Maint Plan

	if @@rowcount < 1
		begin
		print ''
		Print '***	No rows updated		***'
		print ''
		end
	

	print 'After Update' 
	select  [Plan Owner.Plan Name]	=
		rtrim(quotename(rtrim(isnull(b.name,'NULL')))+'.'+quotename(rtrim(a.name)))
	from
		msdb.dbo.sysdtspackages90 a
		left join
		sys.syslogins b
		on a.ownersid = b.sid
	where
		a. packagetype = 6	-- 6 = Maint Plan

	goto Script_Exit
	end

if	-- Verify SQL Server version is 2008 or 2008 R2
	left(convert(varchar(50),serverproperty ('ProductVersion')),3) = '10.'
	begin

	print 'Changing SQL 2008 or SQL 2008 R2 Maintanance Plan Owners'

	print 'Before Update' 
	select  [Plan Owner.Plan Name]	=
		rtrim(quotename(rtrim(isnull(b.name,'NULL')))+'.'+quotename(rtrim(a.name)))
	from
		msdb.dbo.sysssispackages   a
		left join
		sys.syslogins b
		on a.ownersid = b.sid
	where
		a. packagetype = 6	-- 6 = Maint Plan

	print 'Update plan owner to '+isnull(@new_owner,'NULL')
	update	msdb.dbo.sysssispackages  
	set 
		ownersid = suser_sid(@new_owner)
	where
		ownersid <> suser_sid(@new_owner)	and
		suser_sid(@new_owner) is not null	and
		packagetype = 6		-- 6 = Maint Plan

	if @@rowcount < 1
		begin
		print ''
		Print '***	No rows updated		***'
		print ''
		end

	print 'After Update' 
	select  [Plan Owner.Plan Name]	=
		rtrim(quotename(rtrim(isnull(b.name,'NULL')))+'.'+quotename(rtrim(a.name)))
	from
		msdb.dbo.sysssispackages   a
		left join
		sys.syslogins b
		on a.ownersid = b.sid
	where
		a. packagetype = 6	-- 6 = Maint Plan

	goto Script_Exit

	end

Invalid_Version:

print 'Invalid SQL Version: '+convert(varchar(50),serverproperty ('ProductVersion'))

Script_Exit: