SQL Server 2016 – Find Service Accounts for Instances

dmvinstanceservice-accountssql serversql-server-2016

I have the following query that gives me one line for each sql server instance installed in the current server:

--list the instances in the current server
--marcello miorelli
--16-sep-2016

            DECLARE @GetInstances TABLE
            ( Value nvarchar(100),
             InstanceNames nvarchar(100),
             Data nvarchar(100))

            Insert into @GetInstances
            EXECUTE xp_regread
              @rootkey = 'HKEY_LOCAL_MACHINE',
              @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
              @value_name = 'InstalledInstances'

            --Select InstanceNames from @GetInstances 
            Select * 
            from @GetInstances 

I have a server with 2 instances, when I run the above query there, I get the following result:

enter image description here

Now for EACH of those instances – or for a single instance, be it named or not, I would like to know at hand, information about the sql server services account similar to what is returned from the queries below:

select * from
sys.dm_server_services

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;

how can I achieve that?

Best Answer

DMV is specific to a particular instance, so no way to get info for all instances of sql server running on a given machine.

2 ways to do it -

  1. Use your script to read registry and dump the info into a temp table and then join either sys.dm_server_services and/or sys.dm_server_registry depending on your needs. You can possibly join both the dmvs using the ImagePath
  2. use powershell - dbatools - Find-DbaInstance and then pipe the discovered instances to any other service related commands for additional info. I have used Boe Prox's Get-SQLInstance to discover sql server instances on a given machine.