How to Find Service Account for SQL Server and SQL Server Agent? – Interview Question of the Week #179

Question: How to Find Service Account for SQL Server and SQL Server Agent?

Answer: As they say – There are more ways than one to skin a cat. In this blog, we will find multiple methods to find out service account used for SQL Server service and SQL Agent Service

How to Find Service Account for SQL Server and SQL Server Agent? - Interview Question of the Week #179 service-800x249

Method 1 – SQL Server Configuration Manager

We can open SQL Server Configuration Manager for respective version. Once opened, click on “SQL Server Services” and then look for “Log On As” column to get service account.

How to Find Service Account for SQL Server and SQL Server Agent? - Interview Question of the Week #179 svc-acct-01

Method 2 – Services applet or services.msc

Start > Run > Services.msc

How to Find Service Account for SQL Server and SQL Server Agent? - Interview Question of the Week #179 svc-acct-02

Method 3 – Using T-SQL

In new versions of SQL Server, there is a catalog view – dm_server_services which can be used to get same details.

SELECT servicename, service_account
FROM sys.dm_server_services
GO

Method 4 – Using ERRORLOG

First, you should know how to get to ERRORLOG file for an instance. There are multiple ways. Refer my earlier blog on the same topic.

SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

In ERRORLOG, we can look for line like below. (mostly Line # 9)

2018-06-22 20:00:01.190 Server       The service account is ‘NT Service\MSSQLSERVER’. This is an informational message; no user action is required.

Do you know of any other method?

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Log, SQL Scripts, SQL Server, SQL Server Security, SQL Server Services
Previous Post
How Much Work Each Processor (CPU) is Doing in SQL Server? – Interview Question of the Week #178
Next Post
How to Check Edition Specific Features Enabled In SQL Server? – Interview Question of the Week #180

Related Posts

4 Comments. Leave new

  • Ramón Quintana
    May 29, 2019 12:42 am

    Hi, I got a error after to know the account name:

    WMI Povider error:
    The service cannot be started, either because it is disabled or because it has no enable devices associated with it
    [0x80070422]

    Reply
  • Why does this not give the results for the SQL Reporting Services. That is odd.

    Reply
  • Thank you. Was able to copy and paste the NT service account into the security panel. Click check names and then give all but “Full control” to the subdirectory for the data files.
    Thankfully they used F:\DATA, G:\DATA., I:\Data and J:\Data which made it easy.
    Also, when attaching a database the .mdf file says where the files should go but they may not be found. I had to edit the location for all the .ndf and .log files. Several times due to errors. Did not save as a script.

    Reply
  • If you still happen to be running SQL 2008 or less and you really need to use T-SQL to get the SQL Server account you can also use the following method which makes use of the whoami command. It’s not ideal because you have to allow xp_cmdshell which comes with risks but I found myself needing to do this today so I thought I’d share.

    IF OBJECT_ID(‘tempdb..#output’) IS NOT NULL DROP TABLE #output

    CREATE TABLE #output (line VARCHAR(1000) NULL)

    INSERT #output
    EXEC xp_cmdshell ‘whoami’

    SELECT TOP 1 line
    FROM #output

    Reply

Leave a Reply