That’s simple, setup a job and run the procedure and schedule it runs when SQL Server agent started. See the problem here? My title is “when SQL Server Start”, not “When SQL Server Agent”. SQL Server can start without agent. Procedure in SQL Server can be started automatically when SQL Server starts. In order to do that, you have to set “scan for startup procs” through sp_configure function, and then use sys.sp_procoption to set a procedure to be launched as server started. See example below.

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'scan for startup procs', 1
reconfigure with override
exec sp_configure 'scan for startup procs'
go
alter procedure simpletest
as
begin
 waitfor delay '01:00:00'
 return
end
go
exec sys.sp_procoption 'simpletest','startup', 1

Once a procedure marked as start up automatically, it will be launched by server as it starts in a user session (session id may be less than 50). You are able to kill that session while it’s running. The login name is sa. When the procedure is started while SQL Server Starting, it will also be logged

2009-09-15 00:50:09.01 spid7s Launched startup procedure 'simpletest'.

There are some restrictions for creating auto-start procedure:

• You have to be a sysadmin
• The owner of procedure must be dbo
• The procedure must be in master database
• Procedure must not have a parameter

There are two ways you can determine if a procedure can be started automatically or not.

• ObjectProperty(object_id,’ExecIsStartup’) = 1
• select * from sys.procedures where is_auto_executed = 1