Job steps of type “Operating System (CmdExec)”
1. This walkthrough shall configure “fareast\varunsh” as the owner of the job. Currently “fareast\varunsh” is added only to the Sql Server logins. It is NOT a member of the sysadmin fixed server role.
2. By default, a job of type “Operating System” runs under the security context of Sql Server Agent service account. Due to the product requirement, that account is always a member of the sysadmin fixed server role. Our goal is to ensure that the job owner is a not a sysadmin. Non-sysadmins do not have permissions to execute “Operating system” job steps without using a proxy account. In other words, to run a job step of type “Operating system” with the owner being a non-sysadmin, we have to configure a proxy to run the step. The step would then execute under the security context of the proxy account. We now need to add a proxy.
3. To add a proxy, first add the (non-sysadmin) account under which you will run the job step of type “operating system” in the Credentials Node.
4. Create a proxy for “Operating system” commands and select the newly created credential for the proxy.
This proxy can run only “Operating System” commands and not other commands like “ActiveX Scripts” etc. You can configure different proxies for different type of commands (also called subsystems).
5. Create a new job and set the owner to a non sysadmin user.
6. Create a new job step. The operating system command used for this walkthrough will be to delete a folder. Set the Run as account to the newly created proxy.
7. Click on OK. It gives the following error.
The owner of the job must have access to the proxy account. When the owner of the job is not a sysadmin, it would need access to the proxy to run commands using it. This prevents any account from calling operating system commands through a proxy, when otherwise it wouldn’t have permissions to call operating system commands.
8. Go to the proxy and give the job owner access to it, as shown through the Principals tag.
9. After giving access, start the job. Again it gives an error. Look at the job history.
10. The proxy account needs delete permissions on the folder. Remember the step is executing under the context of the proxy account. Configure a proper ACL on the folder.
11. Run the job again. The job runs successfully and the folder is deleted.
This shows how to run an Operating System job step with the least privileges to get the job done.
In what scenario does the owner of a job, require being a member of sysadmin fixed server role?
The transact Sql, operating system and analysis service tasks can log the output of the job to either a operating system file or to the sysjobstepslogs table in the msdb database. Only if the user executing a job step is a member of sysadmin role, can the output be logged in an operating system file. Otherwise, if the user is not a member of sysadmin role, the output must be logged in the table. Application teams should be advised to log the output to the sysjobstepslogs table, rather than to an operating system file.
After configuring the output to be logged to a file, if you run the job under a non sysadmin owner, it gives this error.