How to allow other users to set an Agent property value

Question

Post reply

How to allow other users to set an Agent property value

Sam Shiell

Ten Centuries

Points: 1373
More actions
March 15, 2010 at 10:40 am

#216669

Hi
I've developed a suite of Server agent jobs for our service desk. A couple of the jobs require them to set property values, which are passed as a run-time parameter to the SSIS package.
Problem is, as the jobs are owned by me the service desk staff cannot enter the property values as this would require editing the job.
Any ideas how to get around this?
I've only just realized that SQL-Server wont allow this and I'm committed to releasing these jobs to production on Thursday. Help!
Thanks
Sam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

how about this idea:

don't let them change the job, nor give them access to edit the job.

Instead, change the job slightly so instead of hardcoding those parameters, change it to get those parameters as a SELECT from a table at the start of the job.

then give the end users the ability to edit the values in that table; as long as they edited the table prior to the job running on whatever schedule it ran on, their new values would be in place.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sam Shiell Ten Centuries Points: 1373 More actions · Answer 2

Hmm, well that is a thought however the service desk people know nothing about fiddling around with tables.

What I might do (but not by Thursday) is to write a simple front-end to a SQL-Server table and then do what you suggested.

In the meantime I've discovered that if I make the service desk people the owner then it all works, so what I'll do is temporarily create a group for them and make that the owner of the package. I think that'll work

Cheers anyway

Sam

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Sam Shiell (3/15/2010)
Hmm, well that is a thought however the service desk people know nothing about fiddling around with tables.
What I might do (but not by Thursday) is to write a simple front-end to a SQL-Server table and then do what you suggested.
In the meantime I've discovered that if I make the service desk people the owner then it all works, so what I'll do is temporarily create a group for them and make that the owner of the package. I think that'll work
Cheers anyway
Sam

A simpler solution is to create a proxy account from your account and give the users access to the proxy and use the proxy to run the SSIS package.

Kind regards,
Gift Peddie