Only a sysadmin can specify '@output_file_name' 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
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.