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.
UPDATE msdb..sysdtspackages90 SET ownersid = SUSER_SID('sa') WHERE name = 'MaintenancePlanName'