You are responsible for managing an instance of SQL Server 2008. You create a stored procedure named MaintenanceTasks_sp to perform maintenance tasks in the tempdb database. You want to ensure that the MaintenanceTasks_sp stored procedure executes automatically when the SQL Server starts.
Which system stored procedure should you use to configure the MaintenanceTasks_sp stored procedure?

A.
sp_configure

B.
sp_serveroption

C.
sp_procoption

D.
sp_addextendedproc

Explanation:

You should use the sp_procoption system stored procedure. When you want to configure a stored procedure toperform some operations on a regular basis, you can configure the stored procedure for automatic execution. Astored procedure that is configured for automatic execution runs automatically every time the SQL server starts.The stored procedures that are configured for automatic execution run with the same permissions as members ofthe sysadmin fixed server role. To configure a stored procedure for automatic execution, the sp_procoption system stored procedure is used. When you create user-defined stored procedures, you should avoid naming your procedures with sp_ as a prefixbecause the sp_ prefix is used by SQL Server for system stored procedures. You can create a Data DefinitionLanguage (DDL) trigger to ensure that all user-defined stored procedures are created with a particular prefix. You can also configure a Policy-Based Management policy that ensures that all user-defined stored procedures startwith a pre-determined prefix.
You should not use the sp_configure system stored procedure because this stored procedure does not allow youto configure stored procedures for automatic execution. The
sp_configure system stored procedure allows you todisplay or change global configuration settings for the current server. You should not use the sp_serveroption
system stored procedure because this stored procedure does not allowyou to configure stored procedures for automatic execution. The sp_serveroption system stored procedure allows you to configure server options for remote servers and linked servers. You should not use the sp_addextendedproc system stored procedure because this stored procedure does notallow you to configure stored procedures for automatic execution.
The sp_addextendedproc system storedprocedure is used to register the name of a new extended stored procedure with SQL Server.

Objective:
Managing SQL Server Security

Sub-Objective:
Audit SQL Server instances.

References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Querying and ChangingData > Stored Procedures > Implementing Stored Procedures > Executing Stored Procedures (Database Engine)> Automatic Execution of Stored Procedures