Wednesday, February 9, 2011

Change Agent Job or Maintenance Plan Ownership

I'm sure you've all seen a SQL Server Agent Job, or Maintenance Plan owned by the person who created the job (ie DOMAIN\JaneDoe).  Not a big deal, I suppose, if that person is never going to leave the company, but... If that login is disabled, those jobs are going to fail.  You can use both of these in the msdb database, to change the ownership of your Agent Jobs, and your Maintenance plans.

    --Use this to alter SQL Server Agent Job owner:
    EXEC msdb..sp_update_job 
     @job_name = 'job_name', 
     @owner_login_name ='login' 

Of course, you need to be sure your new login has adequate permissions.

      --Use this to alter the Maintenance Plan owner to 'sa':
    UPDATE msdb..sysssispackages
    SET [ownersid]=0x01
    WHERE [name]='YourMaintPlanName'
You don't need to worry about changing the ownersid value here, because the SID for 'sa' is always 0x01. 

I typically use 'sa' for my Agent Job and Maintenance Plan ownership.  Another option would be the SQL Service account login.  Any other generic login would be fine, too, as long as it has the appropriate privileges, locally and on the network.  Just keep in mind your business security policies.

No comments:

Post a Comment