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.
