MCITP

MCITP

Tuesday, September 11, 2012

How to find the owner of the maintenance plans in SQL SERVER 2000/2005/2008/2008R2

Run the below script and it will tell you the owner of the maintenance plan.

For SQL 2000.

select plan_name,owner from dbo.sysdbmaintplans

For SQL 2005/2008/2008R2

select * from dbo.sysmaintplan_plans

If you want to change the maintenance plan owner then please use the below command.

For SQL 2000

update dbo.sysdbmaintplans set owner ='sa' where owner ='LoginName'

For SQL 2005


UPDATE
msdb.dbo.sysdtspackages90
SET
OWNERSID = SUSER_SID('YOUR_DOMAIN\APPROPRIATE_ACCOUNT')
WHERE
OWNERSID = SUSER_SID('YOUR_DOMAIN\OLD_ACCOUNT')

For SQL 2008/2008R2


update msdb.dbo.sysssispackages set [ownersid] = suser_sid('sa') where [name] = 'MaintenancePlan'

3 comments:

Followers