SQL Server Agent (9/12): Understanding Jobs and security

SQL Server Agent is the core of all real-time databases. Proxies have a lot of non-obvious uses, so the knowledge of the system is useful for developers or DBAs. This series of articles will be popular to introduce its many uses.

In the previous article in this series, you learned how to start an external program in the SQL Server Agent job step. You can use outdated ActiveX systems, run batch commands from a virtual command prompt, or even start your own programs. Your best option is to use the PowerShell subsystem to run PowerShell scripts. The PowerShell script will allow you to manipulate everything in the system or SQL Server perspective. In this article, you will be earning SQL Server Agent security. For most people, security is a confusing topic and deserves some definite consideration. There are 2 different angles of security in this series that involve the security of running SQL Server Agent jobs, which can be used as a simulated security account to run job steps as Agent jobs. The next article in this series talks about the proxy account, which focuses on security permissions for running SQL Sever Agent and SQL Server Agent jobs.

security required for SQL Server Agent service account

The first article in this series has already talked about choosing the SQL Server Agent service account, and to further understand how SQL Server Agent works, now you need to revisit your decision. If you only want to connect to a job on a local SQL Server instance, you need minimal permissions for the SQL Server service account. In this case, NetworkService is the best option to use as a service account. Using Windows Server 2008r2 and SQL Server R2, this provides a very secure account that can easily authorize the work of SQL Server.

If you want to use 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 SQL Server instance or network share, you will use the system domain user account as the custom service account. You can create a specific account for SQL Server Agent, use a unified account for all SQL Server Agent installations in your organization, or use a different account for each SQL Server Agent instance.

When you select an account for the SQL Server service, your account requires the following permissions:

• In all versions of Windows, the permission to log on as a service ( 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 https://msdn.microsoft.com/zh-cn/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.

Modify 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. Illustration 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 1: Correct way to modify SQL Server Agent account

security roles for SQL Server Agent

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 2). You can view only the jobs you create and view and use the Job Activity monitor (only jobs that you create).

Illustration 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).
Illustration 3, as a member of the SQLAgentReaderRole, you can still see the job node, job Activity Monitor, but now you can see all the jobs on the system, not just what you created.

Illustration 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 illustration 4, with almost full access to SQL Server Agent.

Next Trailer

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.

Original link: http://www.sqlservercentral.com/articles/Stairway+Series/72460/

Reference article: http://www.cnblogs.com/Uest/p/4547568.html

SQL Server Agent (9/12): Understanding Jobs and security