Search

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

Closed
as Fixed Help for as Fixed

32
0
Sign in
 to vote
Type: Bug
ID: 295846
Opened: 8/30/2007 9:25:32 AM
Access Restriction: Public
Duplicates: 454073
4
Workaround(s)
15
User(s) can reproduce this bug
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.
Details (expand)
Product Language
English

Version

SQL Server 2005 SP2 - Standard Edition

Category

Tools (SSMS, Agent, Profiler, etc.)

Operating System

Win2003 Standard Server (SP2)
Operating System Language
US English
Steps to Reproduce
SQL Server in Mixed Authentication mode.
1. Connect to SQL Server via SSMS using an account that's a member of the Domain Admins group (DomainName\DAdmin1).
2. Create a transaction log maintenance plan for "Selected Databases". Select some, but not all, databases.
3. Save the maintenance plan.
4. While still logged in as DomainName\DAdmin1, go to Jobs, create a new job that schedules the maintenance plan to run nightly. The job will be owned by DomainName\DAdmin1. Allow job to run on schedule at least once.
5. In Active Directory, remove the DomainName\DAdmin1 account from every group except Domain Users, but DO NOT disable the account.
6. Allow the job to run on schedule. It will fail, as expected, with "The owner (DomainName\DAdmin1) does not have server access".
7. Edit the Job properties, assign the job a new owner (DomainName\DAdmin2) that is a member of the Domain Admins group.
8. Allow the job to run on schedule. It will succeed.
9. Connect to SQL Server via SSMS with as an account that is a member of the Domain Admins group, but that is NOT the job owner (DomainName\DAdminOther). Edit the maintenance plan, adding a database to it. Save the maintenance plan.
10. Allow the job to run on schedule. It will fail with the message "The owner (DomainName\DAdmin1) does not have server access". This is the original owner, who should no longer have job ownership.
Actual Results
Job failed because its ownership reverted to the account originally used to create the job.
Expected Results
Job should run successfully because its ownership had been changed to a new account that is a member of Domain Admins, and the job had previously been scheduled and run successfully under this account.

Platform

32
File Attachments
0 attachments
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.
Sign in to post a workaround.
Posted by Ajibola1 on 8/30/2012 at 6:44 AM
The workaround provided by ACALVETT and RWhitehead is very good but I think the 'where' clause should be based on the 'Packagetype' column instead of the name column as maintenance plans names do vary. Therefore the two workarounds will look like this:

--SQL 2005

UPDATE
    [msdb].[dbo].[sysdtspackages90]
SET
    [ownersid] = 0x01
WHERE
    [packagetype] = 6

--SQL 2008
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid('sa')
[packagetype] = 6
Posted by RWhitehead on 1/30/2009 at 6:38 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:56 AM
/*This is how to change the owner to dbo*/
--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 ACALVETT on 12/30/2007 at 10:56 AM
You can directly update the owner in msdb.sysdtspackages90
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement