Automatic Execution of Stored Procedures
When you mark stored procedures for automatic execution, these stored procedures are executed every time Microsoft® SQL Server™ 2000 starts.
This is useful if you have operations that you want to perform regularly, or if you have a stored procedure that runs as a background process and is expected to be running at all times. Another use for automatic execution of stored procedures is to have the stored procedure perform system or maintenance tasks in tempdb, such as creating a global temporary table. This ensures that such a temporary table will always exist when tempdb is re-created as SQL Server starts.
A stored procedure that is automatically executed operates with the same permissions as members of the sysadmin fixed server role. Any error messages generated by the stored procedure are written to the SQL Server error log. Do not return any result sets from a stored procedure that is executed automatically. Because the stored procedure is being executed by SQL Server rather than a user, there is nowhere for the result sets to go.
Although stored procedures are set for automatic execution individually, the SQL Server scan for startup procs configuration option can be set to prevent all stored procedures from executing automatically when SQL Server starts.
To set or unset a stored procedure for automatic execution
Transact-SQLTo set or unset the scan for startup procs configuration option