SQL Server services

SQL query to show information about SQL Server services:

• last restarts;
• service account identities (thanks to Simon D. Richards).

This just got a lot easier (if you don't mind this bug omitting some service start times).

SELECT
  servicename
, last_startup_time
, service_account
FROM sys.dm_server_services

services.sql

DECLARE @SqlService VARCHAR(255)
DECLARE @AgentService VARCHAR(255)
 
-- default instance
SET @SqlService = 'MSSQLSERVER'
SET @AgentService = 'SQLSERVERAGENT'
-- named instance?
IF CHARINDEX('\', @@SERVERNAME) > 0
BEGIN
  SET @SqlService = 'MSSQL$' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME) - CHARINDEX('\', @@SERVERNAME))
  SET @AgentService = 'SQLAgent$' + RIGHT(@@SERVERNAME,LEN(@@SERVERNAME) - CHARINDEX('\', @@SERVERNAME))
END
-- convert to registry keys
SET @SqlService = 'SYSTEM\CURRENTCONTROLSET\SERVICES\' + @SqlService
SET @AgentService = 'SYSTEM\CURRENTCONTROLSET\SERVICES\'  + @AgentService
-- get account details
DECLARE @SqlAccount VARCHAR(255)
EXEC xp_regread 'HKEY_LOCAL_MACHINE', @SqlService, 'ObjectName', @SqlAccount OUT
DECLARE @AgentAccount VARCHAR(255)
EXEC xp_regread 'HKEY_LOCAL_MACHINE', @AgentService, 'ObjectName', @AgentAccount OUT
 
;WITH cte (
  [Service]
, [Started]
, ServiceAccount
) AS (
  SELECT
    'SQL Server'
  , create_date
  , @SqlAccount
  FROM sys.databases
  WHERE name = 'tempdb'
 
  UNION ALL
 
  SELECT
    'SQL Server Agent'
  , MAX(agent_start_date)
  , @AgentAccount
  FROM msdb.dbo.syssessions
)
SELECT *
FROM cte