Part of the DBA-Tools Project.
A SQL Server instance is really a handful of Windows services working together, and it is surprisingly common for one of them to be quietly misconfigured. The SQL Server Agent service set to Manual startup instead of Automatic. A service still running under a personal domain account that leaves on password expiry. The Full-Text service left in Disabled state that nobody remembers switching off. None of this shows up as an error until the day the server reboots and a service doesn’t come back with it.
This script inventories every SQL Server–related service on the instance, its startup type, current status, and service account, and flags the combinations that are actually a problem rather than just different.
Why Services Information Matters
The services layer sits underneath everything else a DBA monitors. If SQL Server Agent isn’t running, scheduled jobs silently stop firing, and backups, index maintenance, and any collector job depending on the Agent go quiet with no immediate symptom. If a service account is wrong, you inherit either a security exposure (LocalSystem, unrestricted local access) or an operational fragility (a personal account whose password will eventually expire and take the service down with it).
This is one of the first things worth checking on any server you didn’t build yourself:
- Confirms SQL Server Engine and Agent are both set to start automatically
- Surfaces high-privilege or shared service accounts that should be dedicated instead
- Flags services that are stopped when they should be running
- Confirms cluster node ownership on a Failover Cluster Instance
When to Run This Script
- Routine SQL Server health checks
- Reviewing a server you’ve just inherited or migrated
- After a Windows patch cycle or reboot, to confirm every service came back
- Auditing service account usage across an estate
- Troubleshooting a job that “just stopped running” (check the Agent service first)
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-ServicesInformation
Category : monitoring
Purpose : SQL Server services — startup type, running status, and service account with
risk flags. Surfaces manual/disabled startup on critical services and
high-privilege service accounts (LocalSystem, SYSTEM, NetworkService).
Author : Peter Whyte (https://sqldba.blog/script-get-sql-server-services-information/)
Requires : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
servicename,
startup_type_desc,
status_desc,
process_id,
last_startup_time,
service_account,
is_clustered,
cluster_nodename,
-- Service account risk: built-in high-privilege accounts are a security concern
CASE
WHEN service_account IN ('LocalSystem', 'NT AUTHORITY\SYSTEM')
THEN 'CRITICAL — LocalSystem has unrestricted local access; use a dedicated service account'
WHEN service_account = 'NT AUTHORITY\NETWORK SERVICE'
THEN 'WARN — NetworkService shares identity with other services; prefer a dedicated account'
WHEN service_account LIKE 'NT Service\%'
THEN 'OK — Managed Service Account (virtual account)'
WHEN service_account LIKE '%$'
THEN 'OK — Group Managed Service Account (gMSA)'
WHEN service_account IS NULL OR service_account = ''
THEN 'INFO — service account not visible (insufficient permissions or service not running)'
ELSE 'OK — dedicated service account'
END AS account_risk,
-- Startup type: SQL Engine and Agent should be Automatic
CASE
WHEN startup_type_desc = 'Disabled'
THEN 'CRITICAL — service is disabled; will not start after reboot'
WHEN startup_type_desc = 'Manual'
AND servicename NOT LIKE '%Browser%'
AND servicename NOT LIKE '%Full-text%'
THEN 'WARN — Manual startup; service will not auto-recover after reboot'
WHEN startup_type_desc = 'Manual'
THEN 'INFO — Manual startup (acceptable for Browser/Full-text if not required)'
ELSE 'OK'
END AS startup_risk,
-- Running status
CASE
WHEN status_desc = 'Running' THEN 'OK'
WHEN status_desc = 'Stopped' AND servicename NOT LIKE '%Browser%'
THEN 'WARN — service is stopped'
ELSE 'INFO — ' + status_desc
END AS running_status
FROM sys.dm_server_services
ORDER BY
CASE
WHEN servicename LIKE '%SQL Server (%' AND servicename NOT LIKE '%Agent%' THEN 1
WHEN servicename LIKE '%SQL Server Agent%' THEN 2
ELSE 3
END,
servicename;
The script queries sys.dm_server_services and returns one row per SQL Server–related service with three risk flags: account_risk, startup_risk, and running_status.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Check SQL Server services, startup type, and account risk:
.\run.ps1 Get-ServicesInformation
# To run against a remote sql server:
.\run.ps1 Get-ServicesInformation -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
The exact services returned depend on what’s installed (Engine, Agent, Launchpad, Browser, Full-Text), but the three risk columns are the primary thing to review.
Understanding the Results
Focus on the three risk columns rather than the raw status fields:
- account_risk — CRITICAL means the service runs as LocalSystem, which has unrestricted access to the local machine. WARN means NetworkService, which shares an identity with other services on the box. OK covers managed service accounts (
NT Service\...), group managed service accounts (...$), and dedicated domain accounts. - startup_risk — CRITICAL means a service is Disabled and won’t start after a reboot. WARN means Manual startup on a service that should recover automatically (the Agent is the most common offender). Manual is fine for Browser and Full-Text, which aren’t always required.
- running_status — WARN means a non-Browser service that should be running is currently stopped.
Any CRITICAL is worth acting on directly. WARN is worth a deliberate decision (either fix it or document why it’s intentional) rather than leaving it as an accident of how the instance was built.
Related Scripts
You may also find these scripts useful:
- Database Inventory
- Databases
- Database Snapshot Inventory
- Database Summary
- Job Inventory
- Linked Server and Job Inventory
- Linked Server Inventory
- Login Inventory
- OS and Hardware Info
- Patch Level
Frequently Asked Questions
Why is SQL Server Agent set to Manual instead of Automatic?
Usually an oversight from the original build rather than a deliberate choice. Automatic is correct for almost every production instance, since Agent-dependent jobs (backups, maintenance, collectors) need it running after every reboot without manual intervention.
Is running SQL Server as LocalSystem actually dangerous?
Yes, on a shared or multi-role server. LocalSystem has unrestricted access to the local machine, well beyond what the SQL Server process needs. A dedicated service account, managed service account, or group managed service account (gMSA) keeps the blast radius contained if the account is ever compromised.
Summary
Service configuration is one of those things nobody checks until something has already gone wrong; a job that stopped running, a service that didn’t come back after a patch, an audit asking who has access to what account. It takes seconds to review and rarely changes once it’s set correctly.
Run this script as part of onboarding any server you didn’t build yourself, and again after any Windows patch cycle or cluster failover, to confirm every SQL Server–related service is running under the account and startup type you expect.
Leave a Reply