Microsoft – SQL Server – Error Message – The job failed. The owner () of job MaintenancePlanBackup.Subplan_1 does not have server access.

Microsoft – SQL Server – Error Message –

The job failed.  The owner () of job MaintenancePlanBackup.Subplan_1 does not have server access.

____________________________________________________________________________________

Traced error back to the database maintenance plan no longer having access to MS SQL Server Instance due to any of many reasons:

  1. Employee leaving company
  2. Windows group (BUILTIN\Administrators) removed from SQL Instance permission list

________________________________________________________________

To determine which Database Maintenance Plan, corresponding database job, and the respective owners – issue a query such as this:

select

            tblJob.[name] as jobName
         ,  tblJob.owner_sid as JobOwnerID
         ,  suser_sname(tblJob.owner_sid) as JobOwnerName
         ,  tblMaintenancePlan.id as planID
         ,  tblMaintenancePlan.create_date as planCreateDate
         ,  tblMaintenancePlan.owner as planOwner

from   msdb.dbo.sysjobs tblJob

       inner join msdb.dbo.sysmaintplan_subplans tblMaintenanceJob

           on tblJob.job_id = tblMaintenanceJob.job_id

       inner join msdb.dbo.sysmaintplan_plans tblMaintenancePlan

           on tblMaintenanceJob.plan_id = tblMaintenancePlan.id

________________________________________________________________
Change Maintenance Plans Owner

(in this case change to sa)

UPDATE [msdb].[dbo].[sysdtspackages90]
SET [ownersid] = suser_sid('sa') --0x01
WHERE [ownersid] != suser_sid('sa') --0x01

________________________________________________________________

Change Job owner for each identified maintenance job

exec sp_update_job
          @job_name = 'MaintenancePlanBackup.Subplan_1'
        , @owner_login_name = 'sa'

3 thoughts on “Microsoft – SQL Server – Error Message – The job failed. The owner () of job MaintenancePlanBackup.Subplan_1 does not have server access.

  1. This fixed my problems for SQL 2005, but it won’t work for SQL 2008 (the object msdb.dbo.sysdtspackages90 doesn’t exist).

    I found the 2008 counterpart – finally

    select * from msdb.dbo.sysssispackages
    — below is the generic – get all – you can also specify by plan name
    UPDATE msdb.dbo.sysssispackages
    SET OWNERsid = SUSER_SID(‘sa’)
    WHERE
    OWNERsid = SUSER_SID(‘xxxxx’)
    — and name = ‘plan name’

    • Jens: Happy SQL Server days. I am having a long one today dealing with remote SqlServerIntegrationServices (SSIS) Connectivity Issue. But, here is hoping the next few days will be better; and if not, it will still be the weekend.
      Cheers.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s