Wednesday, November 4, 2015

Get SQL Server Service Account using T-SQL



Identify the SQL Server Service Account using T-SQL 
You can find the service account information in Services 
Go to Start --> Run --> Services.msc 
Right click on SQL Server Services and go to Properties. 
The account information is available under Log On Tab

And also service account information stored in Windows Registry, using xp_instance_regread extended stored procedure we can read windows registry.  Registry entries are like below 
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLServerAgent
Using below script also we can fetch the information   
DECLARE       @DBEngineLogin       VARCHAR(100)
DECLARE       @AgentLogin          VARCHAR(100)

EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N'KEY_LOCAL_MACHINE',
              @key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
              @value_name   = N'ObjectName',
              @value        = @DBEngineLogin OUTPUT

EXECUTE       master.dbo.xp_instance_regread
              @rootkey      = N'HKEY_LOCAL_MACHINE',
              @key          = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
              @value_name   = N'ObjectName',
              @value        = @AgentLogin OUTPUT

SELECT        [DBEngineLogin] = @DBEngineLogin, [AgentLogin] = @AgentLogin
GO

Output 
in above scenario both Agent and SQL Services are running as Local System 
Using DMVS also we can fetch the information   

SELECT
      servicename
      , service_account
      , startup_type_desc
      , status_desc
      , is_clustered
FROM   sys.dm_server_services


Output :

in above scenario SQL Services is running as NT Service and Agent is running with Domain Account.