Search This Blog

Microsoft SQL Server:SQL Script to get SQL Services for all local instances

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:

Get multiple instances of SQL Server installed on a server and their services:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- 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)