Get Service Account Details of the SQL Agent Service

Question

How can I get the Service Account name for the SQL Agent service for a particular SQL Server (SQL 2005). Is it possible to get using SQL statements or WMI ?

Just asking the DBA would really be the most appropriate way to get this information. I know that I wouldn't want anyone trying to poke around my SQL Server programatically in order to find out what accounts the services are being run under. — HardCode, Sep 06 '11 at 18:33

score 11 · Answer 1 · answered May 26 '15 at 18:07

As Aaron Bertrand pointed out, you can use the undocumented xp_regread in SQL Server 2005 and SQL Server 2008, but there is a better way, starting with SQL Server 2008R2 SP1.

From the article How to identify the SQL Server Service Account in T-SQL, you can use sys.dm_server_services like this:

SELECT  DSS.servicename,
        DSS.startup_type_desc,
        DSS.status_desc,
        DSS.last_startup_time,
        DSS.service_account,
        DSS.is_clustered,
        DSS.cluster_nodename,
        DSS.filename,
        DSS.startup_type,
        DSS.status,
        DSS.process_id
FROM    sys.dm_server_services AS DSS;

SQL Service Account Query Results

score 8 · Answer 2 · edited Aug 30 '13 at 18:19

Since SQL Server runs as a windows service you can use wmic to query the start name.

wmic service where "name Like 'MSSQL%'" get Name , StartName

For me this outputs the following (since I've got multiple instances thoes are included as well)

Name                    StartName
MSSQL$SQLEXPRESS        NT AUTHORITY\NetworkService
MSSQL$SQLEXPRESS2005    NT AUTHORITY\NetworkService
MSSQLFDLauncher         NT AUTHORITY\NETWORK SERVICE
MSSQLSERVER             NT AUTHORITY\NETWORK SERVICE
MSSQLServerADHelper     NT AUTHORITY\NetworkService
MSSQLServerADHelper100  NT AUTHORITY\NETWORK SERVICE
MSSQLServerOLAPService  NT AUTHORITY\NETWORK SERVICE

You can add /NODE to query remote computers. As with any WMI query you will need sufficient privileges in order for this to work

Or the same query using Powershell's Get-WmiObject (Supports remote/multiple computersnames):

Get-WmiObject Win32_Service -ComputerName localhost,W-Remote -Filter "name Like 'MSSQL%'" | ft __Server,State,Name,DisplayName,StartName -AutoSize

Sample Output:

__SERVER State   Name                   DisplayName                         StartName
-------- -----   ----                   -----------                         ---------
W0123456 Stopped MSSQL$SQLEXPRESS       SQL Server (SQLEXPRESS)             NT AUTHORITY\NETWORK SERVICE
W0123456 Running MSSQLSERVER            SQL Server (MSSQLSERVER)            LocalSystem
W0123456 Stopped MSSQLServerADHelper100 SQL Active Directory Helper Service NT AUTHORITY\NETWORKSERVICE
W-REMOTE Stopped MSSQL$SQLEXPRESS       SQL Server (SQLEXPRESS)             NT AUTHORITY\NETWORK SERVICE
W-REMOTE Running MSSQLSERVER            SQL Server (MSSQLSERVER)            LocalSystem

Aaron Bertrand · Answer 3 · 2012-12-18T21:38:52.323

For a default instance:

DECLARE @sn NVARCHAR(128);

EXEC master.dbo.xp_regread
    'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
    'ObjectName', 
    @sn OUTPUT;

SELECT @sn;

For a named instance, you'll need the second argument to be:

    'SYSTEM\CurrentControlSet\services\SQLAGENT$InstanceName',

Now, it may not work because you may not have access to xp_regread, and the location of this property may change from version to version (I only tested 2008, 2008 R2 and Denali - I don't have a 2005 instance handy to check).

In any case you are probably better off asking the DBA (as suggested in a comment) or, if you have access to the physical machine, just checking the service account in the Control Panel.

Note that if you have named instances then you should replace MSSQL$InstanceName with SQLAGENT$InstanceName or else you will be looking at the service account for SQL Database service rather than SQL Agent. — Josh Gallagher, Dec 18 '12 at 21:10

score 3 · Accepted Answer · edited May 26 '15 at 17:36

3

You can use the sc.exe.

To find SQL instances services:

sc \\<remote computer name> query | find /i "sql"

To get configuration:

sc \\<remote computer name> qc <service name from listing above>

edited May 26 '15 at 17:36

KyleMit

30,350
66
462
664

answered Aug 08 '13 at 13:38

lp larsson

46
1

If you want to use powershell, make sure to call it with `sc.exe` so you don't use the alias for `set-content`. As mentioned in [this article](http://blogs.technet.com/b/josebda/archive/2012/03/03/using-windows-powershell-to-run-old-command-line-tools-and-their-weirdest-parameters.aspx), you can also use more Powershell Friendly commands like [`Get-Service`](https://technet.microsoft.com/en-us/library/hh849804.aspx) – KyleMit May 26 '15 at 18:24

score 1 · Answer 5 · answered May 25 '17 at 21:16

I know this is an old thread but here is my solution. This has been tested against SQL Server 2000, 2005, 2008, 2008 R2, 2012, 2014 and 2016.

if (select CONVERT(INT, (REPLACE(SUBSTRING(convert(nvarchar, SERVERPROPERTY('ProductVersion')), 1, 2), '.', '')))) >10
BEGIN
select distinct(service_account) AS SvcAccount from sys.dm_server_services;
END

ELSE
BEGIN
DECLARE @instanceName varchar(100)
set @instanceName    = convert(varchar,SERVERPROPERTY ('InstanceName'))
IF (@instanceName) IS NULL
begin
DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread
    'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
    'ObjectName', 
    @sn  OUTPUT ;

SELECT @sn AS SvcAccount;
END
ELSE
BEGIN 

DECLARE @SQL varchar (500)
SET @SQL  = 'DECLARE @sn NVARCHAR(128); exec master.dbo.xp_regread ''HKEY_LOCAL_MACHINE'', ''SYSTEM\CurrentControlSet\services\SQLAgent$'+@instanceName+''',''ObjectName'', @sn OUTPUT; SELECT @sn AS SvcAccount;'
EXEC (@SQL)

END
END

Get Service Account Details of the SQL Agent Service

5 Answers5

Linked