Find the SQL Server Service accounts using TSQL

Connecting to remote servers every day all day can take up a lot of time, and it is something you do when you don’t know any better.  Sure, there are some great tools to help you manage your remote desktops library (Terminals is my favorite), but why waste the time connecting to a remote server when SSMS can bring you almost all of the information you need, without the hassle of waiting for your session to kick in.

One of the pieces of information you may need to fetch is the accounts that are used to run your SQL services. In SQL 2008 R2 SP1 and later this is a very easy script. In SP1, Microsoft introduced some new DMVs, amongst which was sys.dm_server_services. This happy little tool allows you to read information about your 3 main SQL services (Engine, Agent & FT). The script below will allow you to find which accounts manage your services:

select servicename, service_account from sys.dm_server_services

Previous to this DMV, things are a little bit more complicated. The only way to pull information on your services was to use the undocumented SP xp_regread. Passing in the root, the key and the value, this allows you to find the name of the key you are interested in and output it to a variable. Use the script below to pull service information in any pre 2008 R2 SP1 instances:

DECLARE @reg_key                    VARCHAR(75)
DECLARE @instance                   VARCHAR(16)
DECLARE @server                     VARCHAR(55)
DECLARE @SQL_reg_key                VARCHAR(150)
DECLARE @Agent_reg_key              VARCHAR(150)
DECLARE @SQLservicename             VARCHAR(50)
DECLARE @Agentservicename           VARCHAR(50)

--initalize our key and server name
SET @reg_key = 'system\currentcontrolset\services\'

--name our services
IF CHARINDEX('\',@server) = 0
SET @SQLservicename = 'MSSQLSERVER'
SET @Agentservicename = 'SQLSERVERAGENT'
--set the instance name
SET @instance = RIGHT(@server,LEN(@server) - CHARINDEX('\',@server,1))
SET @SQLservicename = 'MSSQL$' + @instance
SET @Agentservicename = 'SQLAgent$' +@instance

--initalize the keys
SET @SQL_reg_key = @reg_key + @SQLservicename
SET @Agent_reg_key = @reg_key + @Agentservicename

--get the SQL account
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @SQL_reg_key, 'ObjectName', @SQLservicename output
--get the Agent account
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', @Agent_reg_key, 'ObjectName', @Agentservicename output

--return the values
SELECT @SQLservicename as 'SQL Account'
SELECT @Agentservicename as 'Agent Account'

This entry was posted in SQL Server and tagged , , , . Bookmark the permalink.

Leave a Reply

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

You are commenting using your 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