Identifying What Runs at Startup on SQL Server

By: K. Brian Kelley | Read Comments | Print Brian is a SQL Server author and columnist focusing primarily on SQL Server security. Related Tips: More

Problem

My auditors have asked me to identify what runs when my SQL Servers first start up. What am I looking for and how do I get the information back?

Solution

There are a few things to look for when determining what runs at start up for SQL Server and the version of SQL Server also plays into this. We're basically looking for three things:

• Whether or not the default trace is running (SQL Server 2005 and above)
• What stored procedures are marked to start when SQL Server starts
• What jobs are marked to start when SQL Server Agent starts

Default Trace

Starting with SQL Server 2005, a trace that runs at startup was added called the default trace. This keeps track of schema changes and the like. Whether or not the default trace is enabled can be determined by using sp_configure. You'll need to turn advanced options on in order to check the value as shown below.

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE WITH OVERRIDE;
GO 

EXEC sp_configure 'default trace enabled';
GO

Stored Procedures Marked to Execute at Startup

Using sp_procoption, a stored procedure in the master database can be set to start when SQL Server starts. An example of when one might do this is to start a trace each time SQL Server is started, such as when you're on SQL Server 2000 or when the default trace doesn't do all you need. We can tell what stored procedures are marked to execute at startup by using OBJECTPROPERTY(). We'll use the property 'ExecIsStartUp' to identify those stored procedures marked to execute when SQL Server starts.

SELECT [name]
FROM sysobjects
WHERE type = 'P'
  AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;

For the above command, I've intentionally stuck with sysobjects because though it is deprecated starting with SQL Server 2005, it still works on the newer versions of SQL Server as well as SQL Server 2000. Using sys.objects does not work on SQL Server 2000 because it doesn't know what that object is. If you're only dealing with SQL Server 2005 and above, then consider using sys.objects instead.

In SQL Server 2000 if a stored procedure is set to execute at startup, it does. But starting with SQL Server 2005 a configuration option was added that determines whether marked stored procedures are executed or not. That configuration setting is 'scan for startup procs' and the first time a stored procedure is set to run at startup, it gets turned on. However, it could be turned off at a later time for whatever reason, so the below code allows you to check the status.

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE WITH OVERRIDE;
GO 

EXEC sp_configure 'scan for startup procs';
GO

One thing to note is you must look at both the configured value as well as the running value. If the option was turned on since the last time SQL Server was restarted, it'll only show a 1 in the configured value column. This makes sense because there is no point changing the running value. After all, SQL Server has already started!

SQL Agent Jobs Marked to Execute at Startup

Finally, we should also look at SQL Server Agent jobs. Since it's not unusual to see SQL Server Agent set to start automatically when a server starts up, that means any jobs which are set to execute at startup effectively run when SQL Server starts (or at least, the server SQL Server is installed on restarts). This can be a back door, especially if a SQL Server Agent job is created that belongs to "sa" and has a job step to modify permissions within SQL Server. Therefore, it's important to note when such jobs exist so we can investigate further. 

To get at the information, we have to join a few tables in msdb. Here is the query that does it for us:

SELECT j.name AS 'Job' 
FROM msdb.dbo.sysschedules sched
  JOIN msdb.dbo.sysjobschedules jsched 
    ON sched.schedule_id = jsched.schedule_id
  JOIN msdb.dbo.sysjobs j 
    ON jsched.job_id = j.job_id
WHERE sched.freq_type = 64;

Next Steps

For additional reading, refer to these tips

• Automatically Running Stored Procedures at SQL Server Startup
• Default Trace in SQL Server 2005
• Using the Default Trace in SQL Server 2005 and SQL Server 2008

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code