SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server – Script to get Service Account for all local instances

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
      InstanceName	NVARCHAR(100),
      RegPath		NVARCHAR(100),
      LoginName		NVARCHAR(100)

-- Get instance names from Windows registry
INSERT INTO #tempInstanceNames (InstanceName, RegPath)
EXEC   master..xp_instance_regenumvalues
@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
@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

-- get SQL Server Agent path for default instance
INSERT INTO #tempInstanceNames (InstanceName, RegPath)
SELECT 'SQLServerAgent', RegPath
FROM #tempInstanceNames

-- update instance names for SQL Server Agent
UPDATE #tempInstanceNames
SET InstanceName = 'SQLAgent$' + InstanceName
AND InstanceName <> 'SQLServerAgent'
AND InstanceName NOT LIKE '%$%'

-- get account information
SET @SQL = 'DECLARE @returnValue NVARCHAR(100)'
'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


-- 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!


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


Leave a comment on the original post [sqlandme.com, opens in a new window]

Loading comments...