設定 scan for startup procs 伺服器組態選項Configure the scan for startup procs 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 scan for startup procs Transact-SQLTransact-SQL伺服器組態選項。This topic describes how to configure the scan for startup procs server configuration option in SQL Server 2019SQL Server 2019 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 您可以使用 scan for startup procs 選項,在 SQL ServerSQL Server 啟動期間掃描預存程序的自動執行。Use the scan for startup procs option to scan for automatic execution of stored procedures at SQL ServerSQL Server startup time. 如果這個選項設定為 1, SQL ServerSQL Server 會掃描及執行伺服器上所定義的所有自動執行預存程序。If this option is set to 1, SQL ServerSQL Server scans for and runs all automatically run stored procedures that are defined on the server. 掃描啟動程序 的預設值是 0 (不掃描)。The default value for scan for startup procs is 0 (do not scan).

本主題內容In This Topic

開始之前Before You Begin

建議Recommendations

  • 此選項是進階選項,只有具經驗的資料庫管理員或通過認證的 SQL ServerSQL Server 專業人員才可變更。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL ServerSQL Server professional.

  • 您可以使用 sp_configure來設定這個選項的值;然而如果您使用 sp_procoption(用以標示或取消標示自動執行的預存程序),這個選項的值就會自動設定。The value for this option can be set by using sp_configure; however, it will be set automatically if you use sp_procoption, which is used to mark or unmark automatically run stored procedures. sp_procoption 用來將第一個預存程序標示為 autoproc 時,這個選項會自動設定為值 1。When sp_procoption is used to mark the first stored procedure as an autoproc, this option is set automatically to a value of 1. sp_procoption 用來將最後一個預存程序取消標示 autoproc 時,這個選項會自動設定為值 0。When sp_procoption is used to unmark the last stored procedure as an autoproc, this option is automatically set to a value of 0. 如果您使用 sp_procoption 標示和取消標示 autoproc,而且固定在捨棄 autoproc 之前取消其標示,則不需要手動設定這個選項。If you use sp_procoption to mark and unmark autoprocs, and if you always unmark autoprocs before dropping them, there is no need to set this option manually.

安全性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

若要設定 scan for startup procs 選項To configure the scan for startup procs option

  1. 在物件總管中,請以滑鼠右鍵按一下伺服器,然後選取 [屬性] 。In Object Explorer, right-click a server and select Properties.

  2. 按一下 [進階] 節點。Click the Advanced node.

  3. [其他] 下方,從下拉式清單方塊中選取所需的值,將 [掃描啟動程序] 選項的值變更為 True 或 False。Under Miscellaneous, change the Scan for Startup Procs option to True or False by selecting the value you want from the drop-down list box.

使用 Transact-SQLUsing Transact-SQL

若要設定 scan for startup procs 選項To configure the scan for startup procs 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_configurescan for startup procs 選項的值設定為 1This example shows how to use sp_configure to set the value of the scan for startup procs option to 1.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE  
GO  
EXEC sp_configure 'scan for startup procs', 1 ;  
GO  
RECONFIGURE  
GO  
  

後續操作:設定掃描啟動程序選項之後Follow Up: After you configure the scan for startup procs option

伺服器必須重新啟動之後,設定才能生效。The server must be restarted before the setting can take effect.

另請參閱See Also

RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
伺服器組態選項 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
sp_procoption (Transact-SQL)sp_procoption (Transact-SQL)