Monitoring option settings

Monitor option settings by using the sa_server_option system procedure with the either OptionWatchList or OptionWatchAction property to specify database server behavior when particular database option values are changed.

Prerequisites

You must have the SERVER OPERATOR system privilege.

Procedure

Connect to the database.
Call the sa_server_option system procedure with either the OptionWatchList or OptionWatchAction property.

Use the sa_server_option system procedure to instruct the database server to send a message or return an error when an attempt is made to set a database option. Use the OptionWatchList property to create a list of options to monitor, and the OptionWatchAction property to specify the action the database server should take when an attempt is made to set an option that is being monitored.

Results

The option values are monitored.

Example

Call the following stored procedure to instruct the database server to monitor the database options automatic_timestamp, float_as_double, and tsql_hex_constant:

CALL dbo.sa_server_option( 
'OptionWatchList','automatic_timestamp,float_as_double,tsql_hex_constant' );

Call the following stored procedure to instruct the database server to return an error if an attempt is made to set an option specified in the OptionWatchList property:

CALL dbo.sa_server_option( 'OptionWatchAction','ERROR' );