Job Step Properties - Configuration - Parameter Issue

Question

Post reply

Job Step Properties - Configuration - Parameter Issue

Sthornton

SSC Enthusiast

Points: 176
More actions
August 16, 2023 at 1:45 am

#4262206

Hi,
I am fairly certain that both myself and my colleagues could ALL change the configuration parameter on a Job Step, but I am now informed that only a Sys Admin or the job owner can change this value. ( currently on SSMS 18.12.1 )
This is a bit annoying as it makes being able to change the parameter when required redundant.
We have swapped the job ownership to my colleague to give him the ability to modify it.
What is your experience using Configuration Parameters when multiple staff need to run the job, but very occasionally change the parameter?

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

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

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 1

This is indeed a feature that's been requested for some time in SQL Server.

We have created a "jobadmin" SQL account on some servers.

That account owns all user jobs and has the needed auth to execute the steps ( or can use the proxy account )

Users that need to be able to modify jobs, need to log into the instance using that "jobadmin" account.

AD-accounts (that need it) have been granted "SQLAgentOperatorRole" so they can see and start jobs they do not own.

( as in the past ADaccount validation for sqlagent jobs occasionally failed, we only have SQLaccounts for job owner accounts )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Or, have the job query a table for the parameter values and then give yourself permission to edit those. Not ideal, certainly, and a lot more work, but it does get the job done.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sthornton SSC Enthusiast Points: 176 More actions · Answer 3

Hi,

Thanks for confirming that the issue isn't unique to me.

Some food for thought. We will need to tackle it a different way.

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 4

I have to wonder why you have setup a SQL Server agent job - running an SSIS package - as an 'interactive' process in the first place. That is not the intended use of agent jobs and is the root of the problems you are facing.

One method is to have the users run the package from the catalog. This can work in some cases - but often fails due to permission and delegation issues. Another method you can try is to use a stored procedure to execute the package where you can parameterize the call itself. Or - you can use dtexec to execute the package from the command line.

Or - have the users run the package directly in VS where they can make the appropriate changes to the parameters as needed.

Depending on what the package actually does - it may be a better option to move it all to PowerShell, where you can control the input parameters directly.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs