Friday, July 8, 2011

Startup Stored Procedure

We were audited by our internal IT auditors who asked some good questions.  One question I could not immediately answer was:  "Why do some of your SQL instances have the 'scan for startup procs' configuration option set"?  Since we have no stored procedures configured (using sp_procoption) to run at startup (that I'm aware of), I did not know how to answer the question. 

I executed the following to determine what stored procedures (if any) are executing at startup:
select name from sys.sysobjects
where xtype = 'p'and
      OBJECTPROPERTY(id, 'ExecIsStartup') = 1;

The result was:  sp_MSrepl_startup

Replication sets sp_MSrepl_startup as a stored procedure to execute at SQL instance startup. 

Full explanation of the OBJECTPROPERTY function is here.

Mystery solved.

No comments:

Post a Comment