From a previous post we noted that we are flirting with granting our external job manager, Control-M, access to SQLAgentUserRole.
What is written?
So as not to exceed what is written, let us see what MSFT’s official documentation states:
Our guide is “SQL Server Agent Fixed Database Roles” ( Link ).
The general restriction is that it is has to be jobs they own or assigned ownership.
|Create, Modify, and Delete Jobs|
|View Job Properties|
|Execute or Stop jobs|
|View Job History|
|Delete Job History||Explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history on any other job.|
|View registered Operators|
|Assigned Job Operators|
|View proxies they have access to|
What other permissions might be needed?
- Jobs can be written as Activex, OS Command Scripts, SSIS
- In summary, jobs have access to an expansive API Set
- When ran under the same account as SQL Server Agent, it can pose a security risk
- And, that is the impetus behind having SQL Proxies
- SQL proxies can be run has a least privileged Account
SQL Server Agent’s reach is quite expansive and so we have to look at each job and see what areas are touched by the Job Step.
|OS File Permission|
Other Side Effects
Job Step Properties – Advanced
If we access the Job Step property step and reach for the Advanced Tab, we can set an “Output file“.
Log File Summary
Executed as user: ControlM. hello [SQLSTATE 01000] (Message 0)
Warning: cannot write logfile E:\Temp\Hello.log.
Writing to log files is only allowed to jobs that are owned by sysadmin.
Please consider writing log to table. The step succeeded.
We were able to prove that granting the SQLAgentUserRole role is sufficient.
Other permissions to the subsystems ( Proxy, OS File System, Email, Operations, SSISDB) are likely needed, as well.