You are the database administrator for your company. The network contains five SQL Server 2008 computers.
One of the servers named MKTGSQL1 is located in a remote branch office.
You want to enable a helpdesk technician in the branch office to create a job for database maintenance on MKTGSQL1.
You want to ensure that when the technician executes the job steps, the output is written to anoperating system file.
To achieve this, you want to assign the helpdesk technician to an appropriate role. To which role should you assign to the helpdesk technician?

A.
sysadmin

B.
SQLAgentUserRole

C.
SQLAgentReaderRole

D.
SQLAgentOperatorRole

Explanation:

You should assign the helpdesk technician to the sysadmin fixed server role. A job step is an action that isperformed on a database or a server by a job. You must create at least one job step for every job. You canconfigure a job step to run executable programs, operating system commands, Transact-SQL statements,ActiveX and PowerShell scripts, replication tasks, Analysis Services tasks, and Integration Services packages.The output generated by a job can either be written to the sysjobstepslogs system table in the
msdb databaseor to an operating system file. Job steps that run executable programs, operating system commands, Transact-SQL statements, or Analysis Services tasks can write output to both destinations. The output of a job step iswritten to an operating system file only when the user who executed the job has been assigned to the sysadmin fixed server role. When a job step is executed by users who have been assigned the SQLAgentUserRole , SQLAgentReaderRole , or SQLAgentOperatorRole fixed database role, the output of the job step can only bewritten to the sysjobstepslogs system table. You should perform the following steps to create a job step thatexecutes Transact-SQL:
1. Open SQL Server Management Studio, and expand the instance of the Database Engine.
2. Expand the SQL Server Agent node in the Object Explorer.
3. Create a new job or modify an existing job by right-clicking the existing job and selecting the Properties option.
4. Open the Steps page in the Job Properties dialog box, and click the New button to open the New JobStep dialog box as shown in the following image:

5. Type a name for the job step in the Step name textbox.
6. Select Transact-SQL script (T-SQL) from the Type drop-down list.Note: If the Run as drop-down list is available, you can select a different SQL login under which the jobstep will run.
7. Type the Transact-SQL statement batches in the Command box. To use a Transact-SQL file, you canclick the Open button and select the desired Transact-SQL file.
8. Click the Parse button to verify your syntax. If the syntax is correct, a message stating that Parsesucceeded will be displayed.
9. Use the Advanced page to configure the job step options as shown in the following graphic:

10. Select an action from the On success action drop-down list that should be performed if the job succeeds.
11. Type a number from 0 to 9999 in the Retry attempts field to specify the number of retry attempts.
12. Type a number from 0 to 9999 in the Retry interval (minutes) field to specify the retry interval.
13. Select an action from the On failure action drop-down list that should be performed if the job fails.If the job is a Transact-SQL script, you can configure the following options:
? Output file – Specifies the name of an operating system file to which the output of the job stepshould be written. The file is overwritten by default each time the job step is executed. To preventthe output file from being overwritten, select the Append output to existing file check box. Thischeck box is only available to members of the sysadmin
fixed server role.
? Log to table – Specifies that output for the job step should be written to the sysjobstepslogs system table. The table contents are overwritten by default each time the job step is executed. Toprevent the table contents from being overwritten, select the Append output to existing entry intable check box. After the job step is executed, you can view the contents of the table by clickingthe View button.
? Include step output in history – Specifies that output from the job step should be written to thehistory
Note: If you are a member of the sysadmin fixed server role, you can configure the job step to run by using adifferent SQL login. To do this, select the desired SQL login from the
Run as user list. You should not assign the helpdesk technician to the SQLAgentUserRole , SQLAgentReaderRole , or SQLAgentOperatorRole fixed database role. When a job step is executed by a user with these roles, the outputof the job step can only be written to the sysjobstepslogs table, not to operating system files.

Objective:
Maintaining SQL Server Instances

Sub-Objective:
Manage SQL Server Agent jobs.

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 Jobs > Creating Job Steps TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Administration: How-to Topics > Automated Administration How-to Topics (SQL Server Management Studio) >How to: Create a Transact-SQL Job Step (SQL Server Management Studio) TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration >Administration: How-to Topics > Automated Administration How-to Topics (SQL Server Management Studio) >How to: Define Transact-SQL Job Step Options (SQL Server Management Studio)