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 @server = CONVERT(VARCHAR(55),SERVERPROPERTY('ServerName'))
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'