設定 user options 伺服器組態選項Configure the user options Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

此主題描述如何使用 ,在 SQL Server 2019SQL Server 2019 中設定 SQL Server Management StudioSQL Server Management Studio user options Transact-SQLTransact-SQL伺服器組態選項。This topic describes how to configure the user options server configuration option in SQL Server 2019SQL Server 2019 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. user options 選項指定所有使用者的全域預設值。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. user options 選項允許您變更 SET 選項的預設值 (如果伺服器的預設值不適當)。The user options option allows you to change the default values of the SET options (if the server's default settings are not appropriate).

使用者可以使用 SET 陳述式來覆寫這些預設值。A user can override these defaults by using the SET statement. 您可以動態設定 user options 以供新的登入使用。You can configure user options dynamically for new logins. 變更 user options的設定之後,新的登入工作階段就會使用新的設定;目前的登入工作階段則不會受到影響。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

  • 下表列出及描述 user options的組態值。The following table lists and describes the configuration values for user options. 不是所有組態值都彼此相容。Not all configuration values are compatible with each other. 例如,不能同時設定 ANSI_NULL_DFLT_ON 與 ANSI_NULL_DFLT_OFF。For example, ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF cannot be set at the same time.

    ReplTest1Value 組態Configuration DescriptionDescription
    11 DISABLE_DEF_CNST_CHKDISABLE_DEF_CNST_CHK 控制暫時的或延遲的條件約束檢查。Controls interim or deferred constraint checking.
    22 IMPLICIT_TRANSACTIONSIMPLICIT_TRANSACTIONS 如果是 dblib 網路程式庫連接,則控制執行陳述式時是否隱含地啟動交易。For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. IMPLICIT_TRANSACTIONS 設定在 ODBC 或 OLEDB 連接上無效。The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
    44 CURSOR_CLOSE_ON_COMMITCURSOR_CLOSE_ON_COMMIT 控制執行認可作業後資料指標的行為。Controls behavior of cursors after a commit operation has been performed.
    88 ANSI_WARNINGSANSI_WARNINGS 控制彙總警告中的截斷與 NULL。Controls truncation and NULL in aggregate warnings.
    1616 ANSI_PADDINGANSI_PADDING 控制固定長度變數的填補。Controls padding of fixed-length variables.
    3232 ANSI_NULLSANSI_NULLS 控制使用相等運算子時 NULL 的處理方式。Controls NULL handling when using equality operators.
    6464 ARITHABORTARITHABORT 查詢執行過程中發生溢位或除以零的錯誤時終止查詢。Terminates a query when an overflow or divide-by-zero error occurs during query execution.
    128128 ARITHIGNOREARITHIGNORE 查詢過程中發生溢位或除以零的錯誤時傳回 NULL。Returns NULL when an overflow or divide-by-zero error occurs during a query.
    256256 QUOTED_IDENTIFIERQUOTED_IDENTIFIER 評估運算式時區別單引號與雙引號。Differentiates between single and double quotation marks when evaluating an expression.
    512512 NOCOUNTNOCOUNT 關閉每個陳述式結束時傳回的訊息,這些訊息會說明有多少資料列受到影響。Turns off the message returned at the end of each statement that states how many rows were affected.
    10241024 ANSI_NULL_DFLT_ONANSI_NULL_DFLT_ON 更改工作階段的行為,使 Null 屬性與 ANSI 相容。Alters the session's behavior to use ANSI compatibility for nullability. 新定義的資料行若未明確定義 Null 屬性,就允許 Null。New columns defined without explicit nullability are defined to allow nulls.
    20482048 ANSI_NULL_DFLT_OFFANSI_NULL_DFLT_OFF 更改工作階段的行為,使 Null 屬性與 ANSI 不相容。Alters the session's behavior not to use ANSI compatibility for nullability. 新定義的資料行若未明確定義 Null 屬性,則不允許 Null。New columns defined without explicit nullability do not allow nulls.
    40964096 CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL 將字串與 NULL 值串連時傳回 NULL。Returns NULL when concatenating a NULL value with a string.
    81928192 NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT 運算式中發生失去有效位數時產生錯誤。Generates an error when a loss of precision occurs in an expression.
    1638416384 XACT_ABORTXACT_ABORT 如果 Transact- SQL 陳述式引發執行階段錯誤,就回復交易。Rolls back a transaction if a Transact-SQL statement raises a run-time error.
  • user options 中的位元位置與 @@OPTIONS 中的位元位置完全一樣。The bit positions in user options are identical to those in @@OPTIONS. 每個連接都有它自己的 @@OPTIONS 函數,代表組態環境。Each connection has its own @@OPTIONS function, which represents the configuration environment. 登入 \ SQL ServerSQL Server 執行個體時,使用者會收到將目前 user options 值指派給 @@OPTIONS 的預設環境。When logging in to an instance of \ SQL ServerSQL Server, a user receives a default environment that assigns the current user options value to @@OPTIONS. user options 執行 SET 陳述式會影響工作階段的 @@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

不含參數或只含第一個參數之 sp_configure 上的執行權限預設會授與所有使用者。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 以同時設定兩個參數的 sp_configure 來變更組態選項或執行 RECONFIGURE 陳述式時,使用者必須取得 ALTER SETTINGS 伺服器層級權限。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. 系統管理員 (sysadmin)serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

若要設定 user options 組態選項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.

使用 Transact-SQLUsing Transact-SQL

若要設定 user options 組態選項To configure the user options configuration option

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 此範例示範如何使用 sp_configure 設定 user options ,以變更 ANSI_WARNINGS 伺服器選項的設定值。This example shows how to use sp_configure to configure the user options to change the setting for the ANSI_WARNINGS server option.

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) RECONFIGURE (Transact-SQL)
伺服器組態選項 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
SET 陳述式 (Transact-SQL)SET Statements (Transact-SQL)