determine account SQL Services are running under

• andrewkane17

  SSCertifiable

  Points: 7529

  More actions

  November 8, 2005 at 3:02 pm

  #165918

  I am able to check the state of the sql services by using:

  EXEC master.dbo.xp_servicecontrol 'QueryState', 'MSSQLServer'

  EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent'

   

  What I would like to do is find out which accounts the services are running under, I don't want to open up EM for all my boxes. What I would like to do is issue a query, xp_cmdshell that I can job to poll all my servers and tell me what accounts are being used and if they are ever changed. Does any body have any idea how this can be achieved.

  Any help will be much appreciated.

   

  Andrew

• Martin Mojzisek

  SSCrazy

  Points: 2207

  More actions

  November 9, 2005 at 3:14 am

  #602977

  You can try this:

  declare @reg_srv varchar(256),

          @reg_agent varchar(256)

  if serverproperty('instancename') is null

           begin    --default instance

            set @reg_srv='SYSTEM\CurrentControlSet\SERVICES\MSSQLSERVER'

            set @reg_agent='SYSTEM\CurrentControlSet\SERVICES\SQLSERVERAGENT'

           end

           else

            begin   --named instance

            set @reg_srv='SYSTEM\CurrentControlSet\SERVICES\MSSQL$'+cast (serverproperty('instancename') as sysname)

            set @reg_agent='SYSTEM\CurrentControlSet\SERVICES\SQLAgent$'+cast (serverproperty('instancename') as sysname)          

            end

   exec master..xp_regread

          'HKEY_LOCAL_MACHINE', @reg_srv, 'ObjectName'

   exec master..xp_regread

          'HKEY_LOCAL_MACHINE', @reg_agent, 'ObjectName'

  go

• andrewkane17

  SSCertifiable

  Points: 7529

  More actions

  November 9, 2005 at 8:48 am

  #603037

  Martin,

   

  Cheers, I had a quick look and this looks good.  Thanks