{}

Our Brands

Impact-Company-Logo-English Black-01-177x54

Welcome to the Schneider Electric Website

Welcome to our website.
Search FAQs
Script to find SQL Server service account
Issue
How to quickly find the account which the SQL Server service is configured using a SQL query.

Product Line
Struxureware Power Monitoring 7.0.x
Power Monitoring Expert 7.2.x, 8.x, 9.x
ION Enterprise 6.0. x
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R 2, 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 registry or from sys.dm_server_services for versions
starting from SQL Server 2008 R2 SP1 and above. This following script returns SQL Server service account.

To retrieve the service account of 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 begin with SQL Server 2008 R2 SP1 and later versions.

Schneider Electric Thailand

Attachment(s)
FindSQLServiceACCT.sql [665 Bytes]
Related products for Schneider Electric Thailand
Product:
Articles that might be helpful Users group

Discuss this topic with experts

Visit our Community for first-hand insights from experts and peers on this topic and more.
Related products for Schneider Electric Thailand
Product: