Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Change Maintenance Plan Owner
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/18/2011 :  12:50:44  Show Profile  Reply with Quote
An irritating feature of SQL 2005 and 2008 maintenance plans is that it makes the login that creates the plan the owner of the plan and creates the job to run as the login of that user. This is especially annoying for situations where the login becomes invalid, or when the user has access through a group only and does not have an individual login on the server. If you change the job owner as a work around, it changes back to the original creator when you update the maintenance plan.

There is no ability in SSMS to change the owner (that I have found), so I wrote this script. This script changes them to SA, but you can put in any valid login.

There are different updates for SQL 2005 and 2008 because the underlying tables are different.



-- 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:








CODO ERGO SUM
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000