配置 scan for startup procs 服务器配置选项Configure the scan for startup procs Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否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. scan for startup procs 的默认值为 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 将第一个存储过程标记为自动执行过程后,此选项的值自动设置为 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 将最后一个存储过程标记为自动执行过程后,此选项的值自动设置为 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 标记或取消标记自动执行过程,并且始终在删除自动执行过程之前进行取消标记,则无需手动设置此选项。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.

SecuritySecurity

权限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. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。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 EngineConnect to the 数据库引擎Database 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  
  

跟进:在配置 scan for startup procs 选项之后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)