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.
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:
<br /> DECLARE @sn NVARCHAR(128);</p> <p>EXEC master.dbo.xp_regread<br /> 'HKEY_LOCAL_MACHINE',<br /> 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',<br /> 'ObjectName',<br /> @sn OUTPUT;</p> <p>SELECT @sn;<br />
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.
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:
<br /> SELECT DSS.servicename,<br /> DSS.startup_type_desc,<br /> DSS.status_desc,<br /> DSS.last_startup_time,<br /> DSS.service_account,<br /> DSS.is_clustered,<br /> DSS.cluster_nodename,<br /> DSS.filename,<br /> DSS.startup_type,<br /> DSS.status,<br /> DSS.process_id<br /> FROM sys.dm_server_services AS DSS;<br />
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.
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.