SQL Script to find SQL Server Service Account

Issue
How to find the account that the SQL Server service is configured to run a SQL query.

Product Line
Power Monitoring Expert 7.2.x

Power Monitoring Expert 8.x

Power Monitoring Expert 9.x

Power Monitoring Expert 2021

Power Monitoring Expert 2022

Power Monitoring Expert 2023

Power Monitoring Expert 2024


Environment

SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017

Cause
SQL Server needs to be configured with an account with the correct permissions.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

SQL Server service account information can be fetched from the registry or from sys.dm_server_services for versions
starting from SQL Server 2008 R2 SP1 and above. This following script returns the SQL Server service account.

To retrieve the service account of the current SQL Server installation from the registry, log into SQL Server Management Studio and run the attached SQL script (FindSQLServiceACCT.sql).
Alternatively, execute the query below :

DECLARE @DatabaseEngineAccount VARCHAR(100)
DECLARE @SQLAgentAccount VARCHAR(100)

EXECUTE Xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name = N'ObjectName',
@value = @DatabaseEngineAccount output

EXECUTE Xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
@value_name = N'ObjectName',
@value = @SQLAgentAccount output

SELECT @@SERVERNAME           AS SQLInstance,
@DatabaseEngineAccount AS DatabaseEngineServiceAccount,
@SQLAgentAccount       AS SQLAgentServiceAccount

Another method is to query sys.dm_server_services view. This gives the details for all installed SQL Server instances.
It is available beginning with SQL Server 2008 R2 SP1 and later versions.

게시 대상: 슈나이더 일렉트릭 Korea