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

2014-01-23 - General, Security

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:

[sql] DECLARE @sn NVARCHAR(128);

EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
'ObjectName',
@sn OUTPUT;

SELECT @sn;
[/sql]

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.


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:

[sql] SELECT DSS.servicename,
DSS.startup_type_desc,
DSS.status_desc,
DSS.last_startup_time,
DSS.service_account,
DSS.is_clustered,
DSS.cluster_nodename,
DSS.filename,
DSS.startup_type,
DSS.status,
DSS.process_id
FROM sys.dm_server_services AS DSS;
[/sql]

Executed on my local instance it returns this result:

sys.dm_server_services in Action

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.

Categories: General, Security
Tags: , , , , , ,

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

Leave a Reply