How to identify the SQL Server Service Account in T-SQL

Introduction

Every once in a while you need to know the service account that is executing SQL Server, for example to grant SQL Server access to a new windows resource. When you are remoted to the machine running the instance in question already, you can just start the SQL Server Configuration Manager to find out. But sometimes it would just be quicker to use T-SQL for this.

Identifying the SQL Server Service Account the Old (and Hard) Way

In SQL Server 2005 and SQL Server 2008 there was no documented way. Some people used the undocumented xp_regread to find that information in an undocumented place in the registry:

You can find more details about that approach and its inherent dangers here on stackoverflow. (That is where I found above query too.)

This seems to work, but using undocumented ways always runs the risk of suddenly breaking because of a change Microsoft decided to implement.

Identifying the SQL Server Service Account the New Way

Since SQL Server 2008R2 SP1 we have a documented way to get to this information: sys.dm_server_services

sys.dm_server_services gives us information about the current state of the services related to SQL Server that are currently installed.
The information includes the startup type, the startup time the path and file name of the executable and, you guessed it, the account that the service is running under.

Are you smarter than a script kiddie?



Kids can download and run code to hack into your database.  Are you prepared to be hacked by a 5th grader? Watch this exclusive 15 minute video to learn the most critical elements of securing your SQL Server database.

Do you have any holes in your fortress?



Watch this exclusive 15 minute video to learn the most critical elements of securing your SQL Server database.

Data thieves want to cash in on your customers' information.



They'll steal it out of your database if you're not prepared. Watch this exclusive 15 minute video to learn the most critical elements of securing your SQL Server database.

Hackers are coming!



Is your SQL Server ready? Watch this exclusive 15 minute video to learn the most critical elements of securing your SQL Server database.

Is your data an easy target?



Hackers look for the easiest ways to crack into your data. Watch this exclusive 15 minute video to learn the most critical elements of securing your SQL Server database.

Is your database security leaky?



Don't let your data spill out onto the Internet! Watch this exclusive 15 minute video to learn the most critical elements of securing your SQL Server database.

An army of threats await your database!



Is your database secure? Watch this exclusive 15 minute video to learn the most critical elements of securing your SQL Server database.

So all we need to do now to identify the service account of a SQL Server instance is to run a select statement like this:

Executed on my local instance it returns this result:

As you can see, it returns not only the service account for the SQL Server service but also for the SQL Server Agent and the SQL Server Full-Text daemon. Besides of that a lot of additional useful information is returned.

Summary

Before SQL Server 2008R2 SP1 there was no documented way to identify the SQL Server service account of an instance by just using T-SQL. Since then however we can use sys.dm_server_services to get to that account name, not only for the SQL Server service but also for other related services like the SQL Server Agent service.