Page 114 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Server Management Studio, SQL Server Management Objects (SMO), or the SQL Server Agent
stored procedures.

Notes Click SQL Server Agent in Object Explorer of SQL Server Management Studio to
administer jobs, operators, alerts, and the SQL Server Agent service. However, Object
Explorer only displays the SQL Server Agent node if you have permission to use it.

!
Caution Auto-restart should not be enabled for the SQL Server service or the SQL Server
Agent service on failover cluster instances.

SQL Server Agent stores most configuration information in tables located in the msdb database.
SQL Server Agent uses SQL Server credential objects to store the authentication information for
proxies.

7.3.3 Setting Required Permissions

To perform its functions, SQL Server Agent must be configured to use the credentials of an
account that is a member of the sysadmin fixed server role in SQL Server. The account must have
the following Windows permissions:
Log on as a service (SeServiceLogonRight)

Replace a process-level token (SeAssignPrimaryTokenPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
To verify that each of these required Windows permissions is set

1. Click Start, click Control Panel, Administrative Tools, and Local Security Policy.
2. Expand the Local Policies folder, and then click the User Rights Assignment folder.

3. Repeat the following steps for each permission:
i. Right-click a permission (such as Log on as a service), and then click Properties.
ii. In the properties dialog box (for example Log on as a service Properties), verify that
the account under which SQL Server Agent runs is listed.
iii. If it is not listed, click Add User or Group, enter the account under which SQL Server
Agent runs, and then click OK.
Typically, the account selected for the SQL Server Agent is a domain account created for that
purpose and has tightly controlled access permissions. It is not necessary to use a domain
account, but if you use an account on the local computer, SQL Server Agent will not have
permission to access resources on other computers. It is common for SQL Server to need permission
on other computers, for instance when it creates a database backup and stores the file in a
location on another computer.

108 LOVELY PROFESSIONAL UNIVERSITY