I am having a strange problem on a SQL server when setting up a SQL Agent Proxy account to allow non SysAdmin users to run scripts and CMDShell procedures. I have tried on other 3 servers and works a treat. On this specific one, not a chance. I was wondering if anyone could give me a step by step tip on how to set this up and what to check in order to come up with a solution (before I clean the box and reinstall everything from scratch).
In The EM, right click the SQL Server Agent and select Properties.
then make sure under the "Job System" panel & Non-System Admin Job Step Proxy Account, the option "Only User with SysAdmin..." is not checked. When this option is unchecked, the Proxy account will be allowed to run CMDEXEC.