Every SQL DBA, sometime wants to know about SQL Server services running under which account. One day, my good friend and colleague asked me, how to get information about all SQL server services and their account details using TSQL query?
I was wondering because I used to check using configuration manager but he wants to get information about more than 30 servers, so manual checking could not work for him.
Eventually, I came to know that I can get the info from registry using master.dbo.xp_instance_regreadn extended stored procedure and build SQL Server Services Info V_0.1 (Click the query link to download the query) query to get information about SQL Server services and its account details.
A dynamic management View (i.e. sys.dm_Server_Services) also provides information about SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server but not for other services.
Hope this will help some one else too 🙂
Note: After download, you need to change its extension from .key to .sql.
|Service Code||SQL Server Service Code|
|Service Name||Service display name|
|Account Name||Account name, which run the service|