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

 All Forums
 General SQL Server Forums
 Script Library
 Change Maintenance Plan Owner

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-08-18 : 12:50:44
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
   

- Advertisement -