You are the database administrator of your company. The network contains a SQL Server 2008 computer thathas a default instance of SQL Server installed. You want to allow an assistant administrator named Adam to beable to perform the following tasks:
? Execute, start, or stop all local jobs.
? Delete the job history for any local job.
? Enable or disable all local jobs and schedules.
To which role should you add Adam to allow him to perform these tasks without giving him unnecessarypermissions?
A.
sysadmin
B.
SQLAgentReaderRole
C.
SQLAgentUserRole
D.
SQLAgentOperatorRole
Explanation:
You should add Adam to the SQLAgentOperatorRole fixed database role. Membership in this role will allowAdam to execute, start, or stop all local jobs, delete the job history for any local job, and enable or disable all localjobs and schedules. The SQLAgentUserRole , SQLAgentReaderRole , and SQLAgentOperatorRole roles arefixed database roles in the msdb database that prevent users who are not members of the sysadmin fixed serverrole from accessing the SQL Server Agent. The SQLAgentUserRole fixed database role is the least privilegedrole that provides permission on operators, local jobs, and job schedules. The SQLAgentReaderRole fixeddatabase role includes all permissions provided by the SQLAgentUserRole role. Additionally, membership in the SQLAgentReaderRole fixed database role enables users to view the list of multiserver jobs and their propertiesand history. The SQLAgentReaderRole fixed database role also allows users to view the list of all jobs and jobschedules including jobs created by other users. The SQLAgentOperatorRole fixed database role includes allpermissions provided by SQLAgentUserRole and SQLAgentReaderRole . In addition to these permissions, themembers of the SQLAgentOperatorRole fixed database role can execute, start, or stop all local jobs and deletethe job history for any local job. Members of this role can also enable or disable all local jobs and job scheduleson the server. You should not add Adam to the sysadmin fixed server role because it provides access to all functionalities ofSQL Server Agent, including viewing the properties and configuring the SQL Server Agent. Therefore, addingAdam to the sysadmin fixed server role will provide him unnecessary permissions. You should not add Adam to the SQLAgentReaderRole fixed database role because membership in this role willnot enable Adam to perform tasks specified in this scenario. The
SQLAgentReaderRole fixed database roleincludes all permissions provided by the SQLAgentUserRole fixed database role, and also enables users to viewthe list of multiserver jobs and their properties and history. You should not add Adam to the SQLAgentUserRole fixed database role because membership in this role willnot enable Adam to perform tasks specified in this scenario. The SQLAgentUserRole fixed database role is theleast privileged role that provides permission on operators, local jobs, and job schedules.Objective:
Installing and Configuring SQL Server 2008Sub-Objective:
Configure SQL Server services.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > AutomatingAdministrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security > SQL Server Agent FixedDatabase Roles TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > AutomatingAdministrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security