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
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.
Method 2 – Services applet or services.msc
Start > Run > Services.msc
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)
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
Why does this not give the results for the SQL Reporting Services. That is odd.
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.
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)
EXEC xp_cmdshell ‘whoami’
SELECT TOP 1 line