Automatically Run Stored Procedure at Startup

-- ----------------------------------------------------------------------------
-- sp_procoption
--
--   Sets or clears a stored procedure for automatic execution. A stored
--   procedure that is set to automatic execution runs every time an instance of
--   SQL Server is started.
--
-- Arguments
--
--   @ProcName 'procedure_name'
--   The name of the procedure for which to set an option. procedure is , with no default.
--
--   @OptionName = 'startup'
--   The name of the option to set. The only value for option is "startup".
--
--   @OptionValue = 'on|off'
--   Is whether to set the option on (true or on) or off (false or off). value is varchar(12), with no default.
--
-- Permissions
--
--   Requires membership in the sysadmin fixed server role.
-- ----------------------------------------------------------------------------

--
-- To set a stored procedure for automatic execution at startup:
exec sp_procoption
    @ProcName    = '<procedure_name>',
    @OptionName  = 'startup',
    @OptionValue = 'on';

--
-- To stop a stored procedure from executing automatically at startup:
exec sp_procoption
    @ProcName    = '<procedure_name>',
    @OptionName  = 'startup',
    @OptionValue = 'off';



--
-- To create a system stored procedure, capable of running at startup:
--

use master;
go

--
-- check/drop existing object:
if object_id('sp_jonlabelle_startup') is not null
    begin
        drop procedure dbo.sp_jonlabelle_startup;
    end
go

--
-- create the system stored proc (must use "sp_" prefix)
create procedure dbo.sp_jonlabelle_startup
as
    begin
        set nocount on;
        print 'startup stored proc executed.';
        return 0;
    end
go

--
-- mark the stored proc as a system object:
exec sp_ms_marksystemobject 'dbo.sp_jonlabelle_startup';
go

--
-- make the stored proc run automatically at SQL instance startup:
exec sp_procoption
    @ProcName    = 'dbo.sp_jonlabelle_startup',
    @OptionName  = 'startup',
    @OptionValue = 'on';
go