SQL Server Agent security
As was discussed at the beginning of this chapter, SQL Server Agent is a service that logs itself into the host operating system. However, we should also take into consideration two more things about security – who is allowed to create jobs and what the user context of the executed job steps is. The following paragraphs explain how to properly secure SQL Server Agent.
SQL Server Agent security roles
SQL Server Agent is almost useless without jobs. As jobs execute a bunch of repetitive administrative tasks, such as backups, database consistency checks, and more, we should carefully control who is allowed to define jobs. Most jobs created in real-life scenarios are created by members of the sysadmin group, but sometimes the DBA is asked to allow job creation for users who are not DBAs. In this case, the job creation and execution rights are pre-defined in the msdb database with the following database roles, sorted from the weakest role to the strongest...
