How to find out the account(s) under which the sql server is running?

Question

I have just created a proxy and I need to grant access to the proxy to the account that runs the sql server agent. It will probably be a domain account.

How can I find it using T-SQL?

Answer 1

In SQL Server 2005 and SQL Server 2008 there was no documented way. So undocumented command xp_regread was used to get the result

DECLARE @sn NVARCHAR(128);

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

SELECT @sn;

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

You can use below query to find the same:

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;