scan for startup procs Option
Use the scan for startup procs option to scan for automatic execution of stored procedures at Microsoft® SQL Server™ startup time. If this option is set to 1, SQL Server scans for and executes all automatically executed stored procedures defined on the server. The default value for scan for startup procs is 0 (do not scan).
The value for this option can be set using sp_configure; however, it will be set automatically if you use sp_procoption, which is used to mark or unmark stored procedures as automatically executed (autoprocs). When sp_procoption is used to mark the first stored procedure as an autoproc, this option is set automatically to a value of 1. When sp_procoption is used to unmark the last stored procedure as an autoproc, this option is automatically set to a value of 0. If you use sp_procoption to mark and unmark autoprocs, and always unmark autoprocs before dropping them, there is no need to set this option manually.
scan for startup procs is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change scan for startup procs only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.
To set the scan for startup procs option
Transact-SQL