Unable to determine if the owner of job has server access

On Wednesday, I was working with my team member to fix some of the jobs failing on a SQL Server 2005 instance. All these jobs were failing with the following error.

Unable to determine if the owner (domainlogin) of job User Databases.Backup - User Databases has server access
(reason: Could not obtain information about Windows NT group/user 'domainlogin', error code 0x534. [SQLSTATE 42000] (Error 15404))

These jobs were executing few Backup and Rebuild Index Maintenance Plans. As the error message stated, the Job Owner did not have access on the instance. The user had access when the Maintenance Plans were created but not anymore.

If the jobs are failing because the job owner does not access, the usual approach is to change the Job Owner to a login which has requisite privileges on the SQL Server Instance. But in this case it would not help. The job owner can be changed. Since these jobs are created by the Maintenance Plans, any modifications done to the jobs would be wiped out if the Maintenance Plan is modified. Just opening the Maintenance Plan and clicking on the Save button is enough to undo all the changes done to the corresponding SQL Agent jobs.

Next step? Modify the Owner of the Maintenance Plans. But SSMS does not have an option to modify the Maintenance Plan owner through GUI. Like Andrew Calvett mentioned in this post, modifying the ownersid column in sysdtspackages90 changes the owner of a Maintenance Plan.

Executing the below query would list the current owner for all the Maintenance Plans in an instance.

SELECT name as PackageName, suser_sname(ownersid) as Owner
FROM msdb..sysdtspackages90
ORDER BY name

The output would be like the one below.

We decided to change the Maintenance Plan ownership to sa login. Executing this script would do it for us.

UPDATE msdb..sysdtspackages90
SET
ownersid = SUSER_SID('sa')
WHERE name = 'MaintenancePlanName'

2 thoughts on “Unable to determine if the owner of job has server access

  1. Pingback: Tweets that mention Unable to determine if the owner of job has server access | SQL Server DBA Diaries of Pradeep Adiga -- Topsy.com

  2. Jonathon E.

    Helpful post. SQL Server 2008 R2 has ‘sysdtspackages’ without the 90. And I didn’t find anything in that table. My jobs were being created by entries shown in ‘sysssispackages’.

    Reply

Leave a Reply