Earlier on my blog I posted on How to get SQL Server Service Account using T-SQL. That works on a single instance. If you multiple instances of SQL Server installed on a server you can use below script to get SQL Server and SQL Server Agent service account information for all local instances:

-- Script to get Service account details for SQL Server and SQL Server Agent
-- Create Temp Table to store instance names
CREATE TABLE #tempInstanceNames
(
      InstanceNameNVARCHAR(100),
      RegPathNVARCHAR(100),
      LoginNameNVARCHAR(100)
)
-- Get instance names from Windows registry
INSERT INTO #tempInstanceNames (InstanceName, RegPath)
EXEC   master..xp_instance_regenumvalues
@rootkey = N'HKEY_LOCAL_MACHINE',
@key     = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'
-- Get instance names from Windows registry
INSERT INTO #tempInstanceNames (InstanceName, RegPath)
EXEC   master..xp_instance_regenumvalues
@rootkey = N'HKEY_LOCAL_MACHINE',
@key     = N'SOFTWARE\\Wow6432Node\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'
-- Update instance names to service path in Windows registry
INSERT INTO #tempInstanceNames (InstanceName, RegPath)
SELECT 'MSSQL$' + InstanceName, RegPath
FROM #tempInstanceNames
WHERE InstanceName <> 'MSSQLSERVER'
-- get SQL Server Agent path for default instance
INSERT INTO #tempInstanceNames (InstanceName, RegPath)
SELECT 'SQLServerAgent', RegPath
FROM #tempInstanceNames
WHERE InstanceName = 'MSSQLSERVER'
-- update instance names for SQL Server Agent
UPDATE #tempInstanceNames
SET InstanceName = 'SQLAgent$' + InstanceName
WHERE InstanceName <> 'MSSQLSERVER'
AND InstanceName <> 'SQLServerAgent'
AND InstanceName NOT LIKE '%$%'
-- get account information
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'DECLARE @returnValue NVARCHAR(100)'
SELECT @SQL = @SQL + CHAR(13) +
'EXEC master.dbo.xp_regread
@rootkey = N''HKEY_LOCAL_MACHINE'',
@key = N''SYSTEM\CurrentControlSet\Services\' + InstanceName + ''',
@value_name = N''ObjectName'',
@value = @returnValue OUTPUT;
UPDATE #tempInstanceNames SET LoginName = @returnValue
WHERE InstanceName = ''' + InstanceName + '''' + CHAR(13)
FROM #tempInstanceNames
EXEC (@SQL)
-- display information
SELECT InstanceName, RegPath, LoginName
FROM #tempInstanceNames
ORDER BY RegPath, InstanceName
-- drop temporary table
DROP TABLE #tempInstanceNames
-- Script End

The above script will return results in following format:

Result Set:

InstanceName                RegPath                     LoginName

MSSQLSERVER                MSSQL.1                      LocalSystem

SQLServerAgent             MSSQL.1                      LocalSystem

MSSQL$SQL2005DEV           MSSQL.2                      LocalSystem

SQLAgent$SQL2005DEV        MSSQL.2                      LocalSystem

MSSQL$SQLSERVER_2008       MSSQL10_50.SQLSERVER_2008    .\sinhas

SQLAgent$SQLSERVER_2008    MSSQL10_50.SQLSERVER_2008    .\sinhas

MSSQL$SQL2012              MSSQL11.SQL2012              .\Administrator

SQLAgent$SQL2012           MSSQL11.SQL2012              NT AUTHORITY\NETWORKSERVICE

(8 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe

EMail me your questions -> Vishal@SqlAndMe.com

Follow me on Twitter -> @SqlAndMe

Filed under: SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions