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
