6

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?

9

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.