Find service account and status of SQL Service using TSQL

Since SQL server 2008R2 we can use DMV (sys.dm_server_services) to get service account  information, It gives us information about the current state of the services related to SQL Server that are currently installed.

SELECT  servicename,

        startup_type_desc,

        status_desc,

        last_startup_time,

        service_account,

        is_clustered,

        cluster_nodename,

        filename,

        startup_type,

        status,

        process_id

FROM    sys.dm_server_services AS ;

 

In SQL 2008 R1 and 2005 there is way to use registry read command to get same information.

 

DECLARE @sa NVARCHAR(128);

EXEC master.dbo.xp_regread

    'HKEY_LOCAL_MACHINE',

    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',

    'ObjectName',

    @sa OUTPUT;

SELECT @sa;