Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
@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

Comments

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

Loading comments...