---
title: "Configure the user options Server Configuration Option | Microsoft Docs"
ms.custom: ""
ms.date: "03/02/2017"
ms.prod: "sql"
ms.prod_service: "database-engine"
ms.service: ""
ms.component: "configure-windows"
ms.reviewer: ""
ms.suite: "sql"
ms.technology:
- "database-engine"
ms.tgt_pltfrm: ""
ms.topic: "article"
helpviewer_keywords:
- "global default for all users [SQL Server]"
- "users [SQL Server], global defaults"
- "user options option [SQL Server]"
ms.assetid: cfed8f86-6bcf-4b90-88eb-9656e22d5dc5
caps.latest.revision: 25
author: "MikeRayMSFT"
ms.author: "mikeray"
manager: "craigg"
ms.workload: "On Demand"
---
# Configure the user options Server Configuration Option
[!INCLUDE[appliesto-ss-xxxx-xxxx-xxx-md](../../includes/appliesto-ss-xxxx-xxxx-xxx-md.md)]
This topic describes how to configure the **user options** server configuration option in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] or [!INCLUDE[tsql](../../includes/tsql-md.md)]. The **user options** option specifies global defaults for all users. A list of default query processing options is established for the duration of a user's work session. The **user options** option allows you to change the default values of the SET options (if the server's default settings are not appropriate).
A user can override these defaults by using the SET statement. You can configure **user options** dynamically for new logins. After you change the setting of **user options**, new login sessions use the new setting; current login sessions are not affected.
**In This Topic**
- **Before you begin:**
[Recommendations](#Recommendations)
[Security](#Security)
- **To configure the user options configuration option, using:**
[SQL Server Management Studio](#SSMSProcedure)
[Transact-SQL](#TsqlProcedure)
- **Follow Up:** [After you configure the user options configuration option](#FollowUp)
## Before You Begin
### Recommendations
- The following table lists and describes the configuration values for **user options**. Not all configuration values are compatible with each other. For example, ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF cannot be set at the same time.
|Value|Configuration|Description|
|-----------|-------------------|-----------------|
|1|DISABLE_DEF_CNST_CHK|Controls interim or deferred constraint checking.|
|2|IMPLICIT_TRANSACTIONS|For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.|
|4|CURSOR_CLOSE_ON_COMMIT|Controls behavior of cursors after a commit operation has been performed.|
|8|ANSI_WARNINGS|Controls truncation and NULL in aggregate warnings.|
|16|ANSI_PADDING|Controls padding of fixed-length variables.|
|32|ANSI_NULLS|Controls NULL handling when using equality operators.|
|64|ARITHABORT|Terminates a query when an overflow or divide-by-zero error occurs during query execution.|
|128|ARITHIGNORE|Returns NULL when an overflow or divide-by-zero error occurs during a query.|
|256|QUOTED_IDENTIFIER|Differentiates between single and double quotation marks when evaluating an expression.|
|512|NOCOUNT|Turns off the message returned at the end of each statement that states how many rows were affected.|
|1024|ANSI_NULL_DFLT_ON|Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.|
|2048|ANSI_NULL_DFLT_OFF|Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.|
|4096|CONCAT_NULL_YIELDS_NULL|Returns NULL when concatenating a NULL value with a string.|
|8192|NUMERIC_ROUNDABORT|Generates an error when a loss of precision occurs in an expression.|
|16384|XACT_ABORT|Rolls back a transaction if a Transact-SQL statement raises a run-time error.|
- The bit positions in **user options** are identical to those in @@OPTIONS. Each connection has its own @@OPTIONS function, which represents the configuration environment. When logging in to an instance of \ [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)], a user receives a default environment that assigns the current **user options** value to @@OPTIONS. Executing SET statements for **user options** affects the corresponding value in the session's @@OPTIONS function. All connections created after this setting is changed receive the new value.
### Security
#### Permissions
Execute permissions on **sp_configure** with no parameters or with only the first parameter are granted to all users by default. To execute **sp_configure** with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the **sysadmin** and **serveradmin** fixed server roles.
## Using SQL Server Management Studio
#### To configure the user options configuration option
1. In Object Explorer, right-click a server and select **Properties**.
2. Click the **Connections** node.
3. In the **Default connection options** box, select one or more attributes to configure the default query-processing options for all connected users.
By default, no user options are configured.
## Using Transact-SQL
#### To configure the user options configuration option
1. Connect to the [!INCLUDE[ssDE](../../includes/ssde-md.md)].
2. From the Standard bar, click **New Query**.
3. Copy and paste the following example into the query window and click **Execute**. This example shows how to use [sp_configure](../../relational-databases/system-stored-procedures/sp-configure-transact-sql.md) to configure the `user options` to change the setting for the ANSI_WARNINGS server option.
```sql
USE AdventureWorks2012 ;
GO
EXEC sp_configure 'user options', 8 ;
GO
RECONFIGURE ;
GO
```
## Follow Up: After you configure the user options configuration option
The setting takes effect immediately without restarting the server.
## See Also
[RECONFIGURE (Transact-SQL)](../../t-sql/language-elements/reconfigure-transact-sql.md)
[Server Configuration Options (SQL Server)](../../database-engine/configure-windows/server-configuration-options-sql-server.md)
[sp_configure (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-configure-transact-sql.md)
[SET Statements (Transact-SQL)](../../t-sql/statements/set-statements-transact-sql.md)