Retrieve the SQL Service accounts using T-SQL

Useful script for finding out the SQL Server Service Account, and the SQL Agent Service Account.

Works for SQL 2005 & SQL 2008

DECLARE @NamedInstance bit
IF CAST(SERVERPROPERTY('ServerName') AS varchar) LIKE '%\%' SET @NamedInstance = 1 ELSE SET @NamedInstance = 0

DECLARE @ServiceName varchar(50)

IF @NamedInstance = 0
BEGIN
SET @ServiceName = 'MSSQLSERVER'
END
ELSE
BEGIN
SET @ServiceName = 'MSSQL$' + RIGHT(CAST(SERVERPROPERTY('ServerName') AS varchar),LEN(CAST(SERVERPROPERTY('ServerName') AS varchar)) - CHARINDEX('\',CAST(SERVERPROPERTY('ServerName') AS varchar),1))
END

DECLARE @KEY_VALUE varchar(100)
DECLARE @ServiceAccountName varchar(100) 

SET @KEY_VALUE = 'SYSTEM\CurrentControlSet\Services\' + @ServiceName
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @KEY_VALUE, 'ObjectName', @ServiceAccountName OUTPUT
SELECT @ServiceAccountName as 'SQLService Account'
IF CAST(SERVERPROPERTY('ServerName') AS varchar) LIKE '%\%' SET @NamedInstance = 1 ELSE SET @NamedInstance = 0


IF @NamedInstance = 0
BEGIN
SET @ServiceName = 'SQLSERVERAGENT'
END
ELSE
BEGIN
SET @ServiceName = 'SQLAgent$' + RIGHT(CAST(SERVERPROPERTY('ServerName') AS varchar),LEN(CAST(SERVERPROPERTY('ServerName') AS varchar)) - CHARINDEX('\',CAST(SERVERPROPERTY('ServerName') AS varchar),1))
END

SET @KEY_VALUE = 'SYSTEM\CurrentControlSet\Services\' + @ServiceName

EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @KEY_VALUE, 'ObjectName', @ServiceAccountName OUTPUT
SELECT @ServiceAccountName as 'SQLAgent Account' 

Advertisements

One Response to Retrieve the SQL Service accounts using T-SQL

  1. dimi says:

    just great! Thanks a lot.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: