How to get list of all SQL Server Service Accounts using T-Sql

There might be times for audit to list all the service accounts from all SQL Servers, In order get this done we need a flexible SQL script to run and get them listed.

Here is the script:

SET NOCOUNT ON DECLARE @REGISTRYPATH VARCHAR(200) , @NAMEDINSTANCEIND CHAR(1) , @INSTANCENAME VARCHAR(128) , @SQLSERVERSVCACCOUNT VARCHAR(128) , @SQLAGENTSVCACCOUNT VARCHAR(128) , @DTCSVCACCOUNT VARCHAR(128) , @OLAPSTARTUP VARCHAR(128) , @SQLSERVERSTARTUP VARCHAR(128) , @SQLAGENTSTARTUP VARCHAR(128) , @DTCSTARTUP VARCHAR(128) , @OLAPSVCACCOUNT VARCHAR(128) , @SSRSSVCACCOUNT VARCHAR(128) , @SSRSTARTUP VARCHAR(128)   CREATE TABLE #SERVICEACCOUNTS (VALUE VARCHAR(50), DATA VARCHAR(50)) IF @@SERVERNAME IS NULL OR (CHARINDEX('\',@@SERVERNAME)=0) SET @NAMEDINSTANCEIND = 'N' ELSE BEGIN SET @NAMEDINSTANCEIND = 'Y' SET @INSTANCENAME = RIGHT( @@SERVERNAME , LEN(@@SERVERNAME) - CHARINDEX('\',@@SERVERNAME)) END   -- SQL SERVER SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\' IF @NAMEDINSTANCEIND = 'N' SET @REGISTRYPATH = @REGISTRYPATH + 'MSSQLSERVER' ELSE SET @REGISTRYPATH = @REGISTRYPATH + 'MSSQL$' + @INSTANCENAME INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME' SELECT @SQLSERVERSVCACCOUNT = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START' SELECT @SQLSERVERSTARTUP = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS   -- SQL AGENT SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\' IF @NAMEDINSTANCEIND = 'N' SET @REGISTRYPATH = @REGISTRYPATH + 'SQLSERVERAGENT' ELSE SET @REGISTRYPATH = @REGISTRYPATH + 'SQLAGENT$' + @INSTANCENAME INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME' SELECT @SQLAGENTSVCACCOUNT = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START' SELECT @SQLAGENTSTARTUP = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS   -- SSIS SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\MSDTSserver100' --Change to MSDTSserver90 for SQL2005 and MSDTC for SQL2000 INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME' SELECT @DTCSVCACCOUNT = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START' SELECT @DTCSTARTUP = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS   --SSAS   SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\MSSQLSERVEROLAPSERVICE' INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME' SELECT @OLAPSVCACCOUNT = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START' SELECT @OLAPSTARTUP = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS   --SSRS   SET @REGISTRYPATH = 'SYSTEM\CURRENTCONTROLSET\SERVICES\REPORTSERVER' INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'OBJECTNAME' SELECT @SSRSSVCACCOUNT = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS INSERT #SERVICEACCOUNTS EXEC MASTER..XP_REGREAD 'HKEY_LOCAL_MACHINE' , @REGISTRYPATH,'START' SELECT @SSRSTARTUP = DATA FROM #SERVICEACCOUNTS DELETE FROM #SERVICEACCOUNTS     SELECT CAST( SERVERPROPERTY ('SERVERNAME') AS NVARCHAR(128)) AS SERVERNAME , COALESCE ( CAST( SERVERPROPERTY ('INSTANCENAME') AS NVARCHAR(128) ) , 'DEFAULT') AS INSTANCE , @SQLSERVERSVCACCOUNT AS [SQL SERVER ACCOUNT] , @SQLAGENTSVCACCOUNT AS [SQL AGENT ACCOUNT] , @DTCSVCACCOUNT AS [SSIS] , @OLAPSVCACCOUNT AS [SSAS] , @SSRSSVCACCOUNT AS [SSRS] DROP TABLE #SERVICEACCOUNTS

OUTPUT: