How to add/remove stored procedure from auto execution in SQL

Using sp_procoption, you can set a stored procedure for auto execution whenever SQL instance start. This is useful if you want to perform a task regularly and need to have it running as a background process.

exec sp_procoption @ProcName = ['stored procedure name'],
@OptionName = 'STARTUP',
@OptionValue = [on|off]
 

A simple and useful usage is to auto-map the network drive, so that SQL database can be restored from backup files in network drive.

CREATE PROCEDURE sp_AutoMapNetwork AS
BEGIN
EXEC cmd_shell  'NET USE {device} [password]'
END