====== SQL Server services ======

SQL query to show information about SQL Server services:
  * last restarts;
  * service account identities (thanks to [[http://simondrichards.wordpress.com/2012/07/05/find-the-sql-server-service-accounts-using-tsql/|Simon D. Richards]]).

===== SQL Server 2008 R2 SP1 and later =====

This just got a lot easier (if you don't mind [[https://connect.microsoft.com/SQLServer/feedback/details/664557/sys-dm-server-services-last-startup-time-is-always-null-for-sql-agent|this bug]] omitting some service start times). 
<code tsql>SELECT
  servicename
, last_startup_time
, service_account
FROM sys.dm_server_services
</code>

===== SQL Server 2008 and earlier =====

<file tsql 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
</file>

{{tag>tsql xp_regread service_accounts admin configuration}}