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.

Publikováno pro:Schneider Electric Česká republika