Question: How do you find the service accounts for SQL Server and SQL Server Agent? Check each service separately.
There are several ways to find the accounts, and I use whichever is available during an investigation. The Database Engine and Agent don’t have to use the same account. Also check that you’re inspecting the intended instance.
T-SQL
From an authorized connection to the instance, query the service DMV:
SELECT servicename, service_account, status_desc
FROM sys.dm_server_services
ORDER BY servicename;
This returns services associated with the current SQL Server instance. It isn’t an inventory of every Windows service or every other SQL Server instance. The service identity also differs from the login used for your current SSMS connection.
SQL Server 2022 and later require VIEW SERVER SECURITY STATE for this view. SQL Server 2019 and earlier require VIEW SERVER STATE.
SQL Server Configuration Manager
Open Configuration Manager for the installed SQL Server version. Select SQL Server Services and read the Log On As column. Widen it until the full account name is visible. If an account needs changing, use Configuration Manager for that separate task. Reading an account name doesn’t require changing it.
Windows Services
Open services.msc and find the engine and Agent service for your instance. Read Log On As, or open the service properties and inspect the Log On tab. A virtual account such as NT Service\MSSQLSERVER is a Windows service identity.
The Database Engine Startup Log
The engine’s startup messages in the ERRORLOG identify its service account. Find the entry by its text rather than assuming a fixed line number. That entry reflects the account at the last startup; it doesn’t identify Agent’s account or prove the current configuration after a later account change. I’d start with the DMV or Configuration Manager when checking the present accounts.
The Agent account is not the engine account, it is a separate service, so check both.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
4 Comments. Leave new
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]
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)
INSERT #output
EXEC xp_cmdshell ‘whoami’
SELECT TOP 1 line
FROM #output