Nineth SQL Server Agent understanding jobs and security

This article is the Nineth article of the SQL Server Agent series, please refer to the original text for details

In the first few of these series, you learned how to start an external program in the SQL Server Agent job step. You can use outdated ActiveX systems, run batch command scripts, and even your own programs. Your best bet is to run PowerShell scripts using the PowerShell subsystem. The PowerShell script will allow you to handle almost all aspects of Windows and SQL Server issues. In this article, you will drill down to SQL Server Agent security.
Security is a confusing topic, and it deserves some definite consideration. This series has two different aspects of security: Running SQL Server Agent jobs securely, and using the security account as the proxy account to run job steps. The next article in this series will discuss the proxy account, which focuses on security permissions for running SQL Server Agent and SQL Server Agent jobs.
security requirements for the SQL Server Agent service account
The first one tells you about choosing the SQL Server Agent service account, but you'll revisit this part of the story based on your deep understanding of how SQL Server Agent operates. If you plan to connect the job only to a local SQL Server instance, the SQL Server Agent service account requires only minimal permissions. In this case, NetworkService is a good choice. In Windows Server R2 and SQL Server R2, provide a very secure account that can easily be granted SQL Server rights.
If you plan to use some of the more advanced features of SQL Server Agent, such as using the CmdExec subsystem or the PowerShell subsystem, or if you want to connect to a remote SQL Server instance or network share, you may want a custom service account (Windows domain user account )。 You can create an account specifically for SQL Server Agent, use a common account for all SQL Server agents, or use a unique account for SQL Server Agent under each instance.
When you select an account for the SQL Server Agent service, your account requires the following security permissions:
--In all Windows versions, as service logon rights (Seservicelogonright)
In Windows Server, the SQL Server Agent service proxy account requires the following permissions:
bypassing traverse checking (SeChangeNotifyPrivilege)
Replace a process level token (SeAssignPrimaryTokenPrivilege)
Adjust memory quotas for processes (Seincreasequotaprivilege)
Log on as a batch job (SeBatchLogonRight)
This list is from SQL Server Books Online http://msdn.microsoft.com/en-us/library/ms191543.aspx. In addition, any account that you select must be a member of the sysadmin server role in the relevant DB instance. Next you will use a proxy account, so these privileges are essential.
Change the service account
If you want to change the service account, you can use the Setup program or SQL Server Configuration Manager to modify it. These programs will correctly grant all the required permissions and security rights to enable the new service account. You should not change the service account directly with Windows. Figure 9.1 shows the use of SQL Server Configuration Manager to change the service account. Please note that you need Windows Administrator privileges to use this program.
Figure 9.1 Changing the SQL Server Agent service account
SQL Server Agent security role
SQL Server Agent has three security roles that are used to control security. These roles are introduced in SQL Server 2005, which are:
->sqlagentuserrole
->sqlagentreaderrole
->sqlagentoperatorrole
Members of the sysadmin server role automatically have all the SQL Server Agent control permissions, just as they have full control of SQL Server. These roles give non-administrator users the right to access and/or control SQL Server agents. These are a role in the msdb database (all SQL Server Agent metadata is saved in SQL Server).
How these roles work: SQLAgentUserRole has the fewest permissions. However, SQLAgentReaderRole and SQLAgentOperatorRole are members of the SQLAgentUserRole, so the permissions you grant to Userrole will automatically be inherited by the other two characters. In addition, SQLAgentOperatorRole is a member of SQLAgentReaderRole, so the same-any permissions granted to Readerroler are automatically extended to operatorrole. Now you can study each subsystem in detail.
SQLAgentUserRole
SQLAgentUserRole members have very limited permissions. They can view the operators they own, local jobs, job schedules. They can also create jobs.
When you are a member of the SQLAgentUserRole database role in msdb, you have the ability to view certain parts of SQL Server Agent (Figure 9.2). You can view only the jobs you create and view and use the Job Activity monitor (only jobs that you create).

Figure 9.2 SQLAgentUserRole Member Connection Object Explorer
SQLAgentReaderRole
Members of the SQLAgentReaderRole database role in msdb inherit SQLAgentUserRole permissions and the ability to use multi-server jobs (12th). You can also view all the jobs on the server, not just your own jobs. However, you can only view jobs that are not created by you (you have control over the jobs you create).
Figure 9.3, as a member of SQLAgentReaderRole, you can still see the job node, job Activity Monitor, but now you can see all the jobs on the system, not just the one you created.
Figure 9.3 SQLAgentReaderRole Member Connection Object Explorer
SQLAgentOperatorRole
The SQLAgentOperatorRole database role in msdb gives the user the SQL Server Agent privilege. It includes all the permissions for the other two SQLAgent database roles, plus view the operator and agent properties, and allows you to view all warnings related to SQL Server Agent.
SQLAgentOperatorRole role members can stop, start, or run local jobs, and can delete the history of local jobs. Role members can enable/disable jobs, and enable/disable job scheduling. But there is a note that they cannot use the GUI to enable/disable work or planning, and they must use system stored procedures (or direct sp_update_job or sp_update_schedule).
SQLAgentOperatorRole role members see a graphical interface similar to Figure 9.4, with almost full access to SQL Server Agent.

Figure 9.4 SQLAgentOperatorRole Member Connection Object Explorer
Next article
SQL Server Agent requires specific Windows and SQL Server permissions for the SQL Server Agent service account. The msdb database has three database roles that allow users who are members of a non-sysadmin server role to have access to SQL Server Agent, based on their level of access requirements. You can use these roles instead of being forced to promote the use or management of SQL Server Agent jobs for members of the sysadmin server role.
In our next article, we will start using the SQL Server Agent service account in the job step to control security rights. Each operating subsystem has different security considerations, and the proxy account is targeted for this security.

Nineth SQL Server Agent understanding jobs and security