0

I have developed an async batch system using the Agent Jobs in SQL Server 2017. It is working really well. I have one issue with setting up the operations staff to run this. We get the following error

Only a sysadmin can specify '@output_file_name' parameter for a jobstep

The user has been set up on the msdb with the following Database role membership; SQLAgentOperatorRole SQLAgentReaderRole SQLAgentUserRole

What it looks like happening is that the user can create (and delete) agents, but when we try and run the code then error above comes up which seems very odd.

For now I have temporarily granted sysadmin to get him working, but quite keen to revoke those right if we can resolve this.

Any help would be appreciated

Regards Ben

0

To the best of my knowledge, this is a hard rule in SQL Server. See for instance here: https://docs.microsoft.com/en-us/sql/ssms/agent/job-step-properties-new-job-step-advanced-page.

You can of course wish for a change in Agent security architecture, but I don't hold my breath for that to happen: https://feedback.azure.com/forums/908035-sql-server.

In the end, perhaps you can work ut an interface with wrappers, signed procedures that creates your jobs, so they are owned by sysadmin without making this person a sysadmin. I can't say for sure whether it'll work out for you in the end, though, but perhaps worth looking into. This would be a good start: http://www.sommarskog.se/grantperm.html.

2
  • Thanks Tibor - very helpful. I will see what I can engineer to get this to work. The async batch process that I have developed is extremely useful. In its current form it can do jobs asynchronously or in series. Plans to add branching, error direction, date sequences and percent complete features.
    – Ben Watson
    Oct 13 '20 at 4:14
  • Sounds interesting! Agent hasn't evolved, pretty much, at all over the years regarding scheduling functionality. The one thing that makes me the most doubtful with the signing approach is if that scopes you to a database (user) and not the instance (login) and perhaps there's a hard-wired check for that specific server role. ...And following the source code I now see such a check for sysadmin. Might limit your options further, just as an FYI.. Oct 13 '20 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.