August 16, 2023 at 1:45 am
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?
August 16, 2023 at 7:52 am
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
August 16, 2023 at 4:58 pm
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
August 17, 2023 at 12:00 am
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.
August 17, 2023 at 4:48 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply