Search This Blog
Showing posts with label Get Current SQL Services. Show all posts
Showing posts with label Get Current SQL Services. Show all posts

Microsoft SQL Server: Get Current SQL Server Services using T-SQL(single Inst)

SQL Server service account information is stored in Windows Registry database. You can get this information from Services Console or SQL Server Configuration Manager.
To get account information from Services Console:
1. Go to Start > Run > Services.msc
2. Right Click on SQL Server Service, i.e. “SQL Server (InstanceName)” and go to properties
3. The account information is available under Log On tab:
image
We can also get this information using T-SQL, we can use extended system stored procedurexp_instance_regread to read Windows Registry. Use below code to get information for SQL Server and SQL Server Agent Services:
DECLARE       @DBEngineLogin       VARCHAR(100)
DECLARE       @AgentLogin          VARCHAR(100)

EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N’HKEY_LOCAL_MACHINE’,
              @key          = N’SYSTEM\CurrentControlSet\Services\MSSQLServer’,
              @value_name   = N’ObjectName’,
              @value        = @DBEngineLogin OUTPUT

EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N’HKEY_LOCAL_MACHINE’,
              @key          = N’SYSTEM\CurrentControlSet\Services\SQLServerAgent’,
              @value_name   = N’ObjectName’,
              @value        = @AgentLogin OUTPUT

SELECT        [DBEngineLogin] = @DBEngineLogin, [AgentLogin] = @AgentLogin
GO
Result Set:
DBEngineLogin        AgentLogin
.\Administrator      NT AUTHORITY\NETWORKSERVICE

(1 row(s) affected)
We can use same registry for default and named instances as xp_instance_regread returns instance specific registry.
With SQL Server 2008 R2 SP1 and above a new server related DMV sys.dm_server_servicesis available which returns information of all instance services. This view also returns additional information about each of services such as startup type, status, current process id, physical executable name. We can also query service account name using this DMV:
SELECT servicename, service_account
FROM   sys.dm_server_services
GO
Result Set:
servicename                                      service_account
SQL Server (SQL2012)                            .\Administrator
SQL Server Agent (SQL2012)                      NT AUTHORITY\NETWORKSERVICE
SQL Full-text Filter Daemon Launcher (SQL2012)  NT AUTHORITY\LOCALSERVICE

(3 row(s) affected)

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)
Subscribe to: Posts (Atom)