Search This Blog


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: