Security7: Managing Permissions for SQL Server Agent

SQL Server Agent objects include alerts (alert), operator (Operator), Job, Dispatch (Schedule), and Proxy, and SQL Server uses the msdb system database to manage the permissions of the agent object. MSDB contains three fixed database roles, in order of permissions from small to large, followed by: SQLAgentUserRole,sqlagentreaderrole, and SQLAgentOperatorRole. If a user is not a member of these three roles or the sysadmin role, the user cannot see the agent node through SSMs's Object Explorer. A user must be a member of the SQLAgent or sysadmin role to use the SQL Server Agent.

In addition, the agent's permissions, as well as the proxy access rights, the agent is used to perform job step, only establish the mapping between the agent and login (login), user-created (Own) job to execute.

One, Agent fixed database role

The sqlagentuserrole role is granted a collection of minimal permissions to the action agent, and its members can only have operation permissions on the local job and job schedule that they create, that is, the members of the role have permissions on owner's own job. And you cannot modify the job ownership (Ownership).

The permissions that the sqlagentuserrole role has:

• Has all the permissions to operate local job and job schedule, including Create, remove (delete), modify (Modify), enable (allow), disable (Disable), execute (Execute), start (start), Stop (stop), etc., but cannot modify the job's ownership relationship (Ownership);
• View the execution log of owned jobs, but do not have permission to delete the history log;
• View the list of available operator;
• View the list of available proxies;

The sqlagentreaderrole role has the same permissions as the sqlagentuserrole role In addition to the view permission of multiserver jobs;

The sqlagentoperatorrole role has the same permissions as the sqlagentreaderrole role In addition to the permission to view the list and attributes of the alert (alert);

SQLAgentUserRole,sqlagentreaderrole, and sqlagentoperatorrole all have permission to operate on jobs that they create and cannot modify the job ownership:

A member of the sysadmin role that has all the permissions of the SQL Server agent to modify the ownership of the job.

Second, the permission to execute the job step

When the job step is executed, the SQL Server agent uses two types of permissions, the job owner and the proxy. If you create a job step that is a Transact-SQL script (T-SQL) type, use the permissions of the job owner to execute the TSQL script, or if you create a job step that is one of the other 11 types, use the delegate's permissions to execute the job step. Before job step executes, the agent impersonates the permissions of Windows user specified by the credential, executes the job step within that permission scope (Security context), and enables job step to have access to resources outside of the SQL Server instance.

Proxy allows job step to execute in a specific security context, where the DBA needs to create the proxy and set the necessary permissions for the proxy.

SQL Server Agent lets the database administrator run each job, step in a security context, have only the permissions req Uired to perform this job step, which is determined by a SQL Server Agent proxy. To set the permissions for a particular job step, you create a proxy, the with the required permissions and then assign Tha T proxy to the job step. A proxy can be specified-more than one job step. For job steps this require the same permissions, you use the same proxy.

1, create an agent

Each proxy is associated with a credential (credential), which defines the security context in which the job step is executed. Create a proxy using SSMs, open the wizard for the new proxy account in the SQL Server Agent directory, enter the new proxy name and the associated credential name, select SQL Server from the list of 11 subsystems (subsystem) Intergration Services package, which is used to execute the SSIS package.

Because the permissions of the agent are determined by the credentials, when creating the agent, in order to effectively control the execution of job step permissions, in general, you need to create a dedicated user account for the agent (proxy), the user is not granted the necessary permissions,

• Create dedicated user accounts specifically for proxies, and only use these proxy user accounts for running job steps.

• Only grant the necessary permissions to proxy user accounts. Grant only those permissions actually required to run the job steps that is assigned to a given proxy account.

2, grant the login Access Proxy permission

In SQL Server, not all login has permission to Access Proxy. Add the server-level (Server-level) security principal (Login) to the proxy to grant access to the proxy so that the user can refer to the proxy to execute the job Step.

In Principals tab, grant login, server role, or msdb database role access to proxy. A proxy can be used in job step only if the principal is granted access to the proxy permission. By default, members of the sysadmin fixed server role have access to all proxies in the instance.

3, referral Agent

If login has access to proxy, or login is a server role with access to proxy, then the user can use proxy in job step, for example, in creating job step, from Run as List of options to access the proxy:

Third, security group

In a Windows domain environment, if the owner of the job is set to security group (SG), members of that group cannot access the job, and only the user can operate the job if the job owner is set to the user's Windows account.

Reference Documentation:

SQL Server Agent

Implement SQL Server Agent Security

SQL Server Agent Fixed Database Roles

Security7: Managing Permissions for SQL Server Agent