Multiserver jobs - EXECUTE permission denied

[Peter Schmitz]

Today, while working on setting up a master/target server configuration which should enable me to define jobs on a single master server and then rolling them out to all other servers defined as targets, I kept running into a specific issue. I could easily execute the job created on the master server, but on the target server, I kept seeing this error:

Executed as user: SchmitzIT\Peter. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 14:34:16 Could not load package "Maintenance Plans\Weekly ReIndex" because of error 0xC001404A. Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_ssis_getfolder', database 'msdb', schema 'dbo'.). Source: Started: 14:34:16 Finished: 14:34:16 Elapsed: 0.047 seconds. The package could not be loaded.

It took me quite a bit of time before figuring out that this message indicates that the user mentioned above needs those permissions on the master server, and not on the target server.

I'll soon write an article about how to setup multiserver jobs administration.