Scan for startup procs
Job details
| Name: | Scan for startup procs | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Check if MS SQL Server automatically scan and run all stored procedures that are set to execute upon service startup. | |
| Long description: | Check if MS SQL Server automatically scan and run all stored procedures that are set to execute upon service startup. | |
| Version: | 1.2 | |
| Default schedule: | 26 1 1 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| return status | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “scan for startup procs” parameter is enabled. |
| disable scan for startup procs | NO | If set to “YES“ the alert will disable “scan for startup procs” (if it is enabled) by running sp_configure stored procedure. |
| history threshold | 365 | The maximum number of days to keep statistics for in the historic tables. |
Job Summary
- Purpose: The purpose of this job is to monitor the status of startup procedures execution on an MS SQL Server setup.
- Why: This job is important because enabling startup stored procedures may pose security risks or unintended behavior upon database service start. Monitoring this setting helps ensure security compliance and detect any unauthorized changes.
- Manual checking: To manually check whether startup procedures are enabled, execute the following SQL command:
SELECT value_in_use FROM sys.configurations WHERE name = 'scan for startup procs'Job Design
- The job periodically checks if MS SQL Server is set to automatically run all stored procedures that execute upon service startup.
- Dependencies include:
- Master procedure responsible for the check itself.
- Current status table that signifies the most recent scan.
- History table that stores records of previous checks.
Implementation Details
- Creation of monitoring and history tables to keep track of current status and changes over time.
- A main procedure named “dbw_scan_for_startup_procs_proc” performs the check by retrieving the parameter value from the system settings, and executes controls based on configurable parameters.
- Alerts are raised based on the status of startup procedure execution and can disable this setting using an SP_Configure stored procedure call if setup accordingly.
Parameter Configuration
The job handles several parameters:
- return status – Specifies the alert level based on the startup procedure status.
- disable scan for startup procs – Allows the job to disable startup procedures if currently enabled.
- history threshold – Controls how long the historical data will be kept.
Reporting
The task configuration outlines detailed reporting, which includes:
- Current configuration settings for the ‘scan for startup procs’ setting, showing whether it’s enabled or disabled.
- Historical graphs showing the parameter value over time.
- Aggregated statistics of how many times and when the checks were conducted.
Tables and Filters
Tables include:
| Configuration Parameter | Current Value | History Tracking |
|---|---|---|
| scan for startup procs | Dynamic real-time fetching | Maintained for a set threshold (default 365 days) |
- Filters envisaged in the job ensure that only instances fitting specific SQL Server versions and types are processed, enhancing performance and relevance of the monitoring.
This job amplifies dbWatch Control Center’s ability to enforce security protocols and ensures that unintended or unauthorized changes to critical server configurations are both monitored and controlled, thereby bolstering system security and stability.