January 5, 2010 at 9:59 am
I changed the scheduling on a maintenance plan, which does my application DB backups. Apparently, when this change occurs, it resets the account that runs the step in the job necessary for the backup.
I have a proxy account set up to run these steps:
SQL Server Agent -> Jobs -> <right click job for job properties> -> Steps -> Edit subplan -> change 'Run as' account
Is this normal behavior? Is there a way to set up the proxy in the maintenance plan itself, so this propagates down to the jobs? No failure email gets set when the job doesn't run, so I have to manually check each system (I have 5 servers with more coming) to see if these ran correctly.
Any experiences with these issues would be helpful....I realize there's lots on the web but nothing I've seen that specifically addresses this. If changing the time that a maintenance plan runs makes me have to change the accounts in the jobs, seems that's a lot of extra work.
TIA
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
March 2, 2010 at 8:38 am
I am having the exact same issue. If I change anything in my "Maintenance Plan" then the SQL Server Agent Subplan looses its "Run as:" setting in the step properties. I am running 32-bit SQL 2005 Developers Edition with SP3. Please help!
For now, instead of going in to the SQL Server Agent, Subplan and then Step, I am running a manual script below every time I update my Maintenance Plan.
-- Here is a the example script. Note: I modified the "name" and I set the "job_id" to 12345 for this example.
select proxy_id, *
from sysjobs
left join sysjobsteps on sysjobsteps.job_id = sysjobs.job_id
where name = 'MaintenancePlan_1.Subplan_1'
select
proxy_id,
*
from sysjobsteps
where job_id = '12345'
begin tran
update sysjobsteps
set proxy_id = 2
where job_id = '12345'
commit tran
March 3, 2010 at 12:10 pm
Thanks Scott, 'preciate the feedback. Interestingly, I found this blog also:
http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
It posts some code to help.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
March 17, 2010 at 1:17 pm
I tried the setting the owner of the maintenance plan to the domain users SID and the job still reverts back to the original owner. Any other ideas?
March 25, 2010 at 1:51 pm
I added the domain user as a principal, assigned it in the proxy and everything worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply