EXECUTE permission denied on object ''xp_sqlagent_enum_jobs''

SQL Server 2005 SP2, v9.00.3042

Last week, I set up a SQL Server login and assigned it to the MSDB role of SQLAgentOperatorRole. A couple of jobs were created and this login was assigned as being the owner of those jobs.

The login was able to successfully edit and execute the jobs. Per the documentation, only these 2 jobs would show up in the jobs list for the login to view.

Now, when the login attempts to expand the jobs list, the following error appears:

EXECUTE permission denied on object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'

I'm not excited about granting explicit execute permissions to extended stored procedures . . . . but will if I have to.

I think the senior DBA changed something that hosed this up.

What should I be looking for?

Also, I should point out that the login was able to successfully able to execute the jobs on our "upgrade test' instance of 2005. Then we created a production instance and restored the MSDB backup of the upgrade instance to the production instance.

I'm thinking the problem is somehow related to this . . . .

EXECUTE permission denied on object ''xp_sqlagent_enum_jobs''

SQL Server 2005 SP2, v9.00.3042

Last week, I set up a SQL Server login and assigned it to the MSDB role of SQLAgentOperatorRole. A couple of jobs were created and this login was assigned as being the owner of those jobs.

The login was able to successfully edit and execute the jobs. Per the documentation, only these 2 jobs would show up in the jobs list for the login to view.

Now, when the login attempts to expand the jobs list, the following error appears:

EXECUTE permission denied on object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'

I'm not excited about granting explicit execute permissions to extended stored procedures . . . . but will if I have to.

I think the senior DBA changed something that hosed this up.

What should I be looking for?

Also, I should point out that the login was able to successfully able to execute the jobs on our "upgrade test' instance of 2005. Then we created a production instance and restored the MSDB backup of the upgrade instance to the production instance.

I'm thinking the problem is somehow related to this . . . .

Execute Package Task with SQL Server Location

Have written a SSIS package which in turn runs a couple of child packages via the Execute Package control flow task. All is fine if I use a Location of File System for the task. However when I deploy to production I'd like the main package and it's child packages to be under SQL Server.

I can set the Execute Package task location to SQL Server after copying my packages into a development SQL box and again it works. However as it's now executing the package from SQL rather than wihin BIDS it doesn't give me the debugging/flow information for the Child package when I run the parent package. It's also a bit of a pain because even though all the packages are part of the same solution if I make a change to a child package I've got to remember to re-save a copy of it to the SQL database.

Have looked into seeing if I can make the location field for the Execute package task configurable so that I could run as a File System package within BIDS but as a SQL package in production but the Location field doesn't seem to be exposed as a property for the Execute Package task so can't set this as an expression or from a configuration.

Does anyone have any advice on using the Executing package task for child packages which are stored in SQL.

What I do, for smaller packages, is to create a boolean variable -- SQLServerExecution -- and then create two flows on the control flow. Each flow does exactly the same thing, except on one flow, there's a precedence constraint that checks for SQLServerExecution to be true (and if so, execute that branch) and the other checks for it to be false.

Setup the false branch's Execute Package tasks to use the file system, and setup the true branch's Execute Package tasks to use SQL Server.

Or, create two master packages -- one for SQL Server packages, the other for file system packages.|||

My only advice is to use the same store type in all the environments. Otherwise support and deployment could become a nightmare as you won't be able to reproduce specific issues.

Sorry, I know that this does not answer your question.

|||

Thank you Phil that makes perfect sense. I'll have a think how easy it is to apply the solution to my parent package.

Think in future I'll also need to lay my C#/OO head to one side when doing SSIS and think a lot more carefully about when to split work off to seperate packages rather than have them as seperate Data Flows/Control flow areas etc within the same package.

|||

It wasn't really a specific question more an "any advice on this" type plea so your advice is most welcome Rafael, thank you.

Execute Package Task in a loop works, but...

Okay, can someone explain why when I execute children packages within a loop that only a couple of them work?

When I first started this I had Child Package 1 and Child Packge 2 working from the Parent Package 1. It would loop through twice (I had 2 organizations in my outer loop) and pass in the Parent Package variables correctly.

I am using a simple "Foreach Item Enumerator" with a collection of string enumerators that are the Names of the children packages - these are assigned to a variable that is scoped to the outer loop.

Now, when I add a new Child Package 3 and set it up the same as the other 2, when it goes to execute Child Package 3 it fails with the error:

"Error: The connection manager "[My Package Name].dtsx" is not found. A component failed to find the connection manager in the Connections collection. "

What in the world have I done? I was under the assumption that since the first 2 packages worked, that any other packages I added would work the same.

I answered my own quesiton on this..

It seems as though when I created the first 2 children packages, I also created the "File" connection for them.

YOU MUST create the "File Connection" for each child SSIS Package you plan to execute.

If anyone knows different, please let me know.

|||

ronemac wrote:

I answered my own quesiton on this..

It seems as though when I created the first 2 children packages, I also created the "File" connection for them.

YOU MUST create the "File Connection" for each child SSIS Package you plan to execute.

If anyone knows different, please let me know.

Wrong I think. You only need one connection manager and you should use expressions to dynamically change the ConnectionString property of that connection manager on each loop iteration.

Regards

-Jamie