Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Identifying What Runs at Startup on SQL Server

MSSQLTips author K. Brian Kelley By:   |   Read Comments   |   Related Tips: More > Auditing and Compliance
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:

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



Last Update: 1/30/2012


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
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

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.