SQL Server service account information is stored in Windows Registry database. You can get this information from Services Console or SQL Server Configuration Manager.

To get account information from Services Console:

1. Go to Start > Run > Services.msc

2. Right Click on SQL Server Service, i.e. “SQL Server (InstanceName)” and go to properties

3. The account information is available under Log On tab:

We can also get this information using T-SQL, we can use extended system stored procedure xp_instance_regread to read Windows Registry. Use below code to get information for SQL Server and SQL Server Agent Services:

DECLARE       @DBEngineLogin       VARCHAR(100)

DECLARE       @AgentLogin          VARCHAR(100)

EXECUTE       master.dbo.xp_instance_regread

              @rootkey      = N’HKEY_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

Result Set:

DBEngineLogin        AgentLogin

.\Administrator      NT AUTHORITY\NETWORKSERVICE

(1 row(s) affected)

We can use same registry for default and named instances as xp_instance_regread returns instance specific registry.

With SQL Server 2008 R2 SP1 and above a new server related DMV sys.dm_server_services is available which returns information of all instance services. This view also returns additional information about each of services such as startup type, status, current process id, physical executable name. We can also query service account name using this DMV:

SELECT servicename, service_account

FROM   sys.dm_server_services

GO

Result Set:

servicename                                      service_account

SQL Server (SQL2012)                            .\Administrator

SQL Server Agent (SQL2012)                      NT AUTHORITY\NETWORKSERVICE

SQL Full-text Filter Daemon Launcher (SQL2012)  NT AUTHORITY\LOCALSERVICE

(3 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe

EMail me your questions -> Vishal@SqlAndMe.com

Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, SQL Agent, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions