Job Owner Reverts to Previous Owner when Scheduled Maintenance Plan is Edited - by DEPeck

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


35
0
Sign in
to vote
ID 295846 Comments
Status Closed Workarounds
Type Bug Repros 17
Opened 8/30/2007 9:25:32 AM
Duplicates 454073 Access Restriction Public

Description

A job for running a nightly transaction log maintenance plan was created and owned by an account that was a member of the Domain Admins group. The account was subsequently removed from the Domain Admins group and the job failed (owner did not have server access), as expected. 

The job owner was changed to another account that is a member of the Domain Admins group, and the job ran successfully for seven days.  On the eighth day, another user, also a member of Domain Admins group, edited the maintenance plan to add a database. After the maintenance plan was saved, job ownership reverted to the original job creator (no longer a Domain Admin) and the job failed on its next scheduled run.
Sign in to post a comment.
Posted by Larry J Andersen on 5/17/2012 at 9:42 AM
Bug is definitely not fixed in 2008 R2 and it's very frustrating.
Posted by Stimo on 2/28/2012 at 12:29 AM
Why is this bug closed? It is still not fixed in SQL Server 2008 R2.
Posted by RWhitehead on 1/30/2009 at 6:40 AM
ACALVETT's script works great, thank you, but not for SQL 2008.

In SQL 2008 I have found the following query works:

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

Note: you can use the same query which ACALVETT provided to locate the name of the Maintenance Plan for the where clause.
Posted by Benjamin Lotter on 3/27/2008 at 5:59 AM
/*Here's how to change the owner of a maintenance plan to dbo in 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'
        
Posted by machine1 on 3/20/2008 at 10:19 AM
As part of a large Microsoft client with 26,000 employees and over 200 SQL licenses, I agree this is a problem and would like for Microsoft to address whether this will be changed for SQL Server 2008.
Posted by Karen Wallace on 2/6/2008 at 10:54 AM
This is a pain in the neck. Every time I do anything to a maintenance plan -- add a step, rename the plan, modify a step, whatever -- all the related jobs get swapped over to being owned by my domain account. We have a one-way trust between our corporate network and our production network, so jobs owned by my domain account fail. If I don't remember to go open every single job created by the maintenance plan, change the owner, and save it, they fail the next time they run. The plan should retain its owner until I change it. Additionally, there should be a way to change to the owner other than manually updating msdb.dbo.sysdtspackages90.
Posted by ACALVETT on 12/30/2007 at 10:58 AM
The feature is easily validated and i feel it should not work this way and introduces risk where by job failures may not be noticed for a period of time leaving a firm exposed.

Given the nature of maintenance plans and the fact you must be a sysadmin to see or create them, surely it makes sense to have the owner as the SQL Service account or a user created by SQL for maintenance plans?
Posted by DEPeck on 8/31/2007 at 11:24 AM
Richard Waymire,

Did you even try to reproduce the problem using the very detailed steps to reproduce that I provided?

Calls to product support -- for an issue that seems to be product related, rather than user related -- require additional time out of my work day, along with negotiations as to whether the call is chargeable to my employer.
Posted by DEPeck on 8/31/2007 at 7:13 AM
Richard Waymire,

Did you even try to reproduce the problem using the very detailed steps to reproduce that I provided?

Calls to product support -- for an issue that seems to be product related, rather than user related -- require additional time out of my work day, along with negotiations as to whether the call is chargeable to my employer.
Posted by Microsoft on 8/30/2007 at 10:56 AM
DEPeck,

Did you call product support for this issue? We really need a support call to understand how this could happen...

-Richard Waymire
Program Manager, SQL Server Agent.