What DMV retains the SSIS, SSRS and SSAS LOG ON account info defined under Services?

• Express12

  SSCoach

  Points: 18514

  More actions

  August 16, 2018 at 5:38 am

  #408092

  What DMV retains the SSIS, SSRS and SSAS LOG ON account info defined under Services?

  This next query is fine for identifying the SQL Server LOG ON and SQL Agent LOG ON - but I also need the SSIS, SSRS and SSAS services Log On:

  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;

  This older query is nice but we're looking for the DMV which holds the same info:

  --*********************************************************************
  --* THE OLD WAY:  prior to SQL 2008R2 SP1         *  
  --*********************************************************************
  -- List SQL Server service account:
  DECLARE @SN NVARCHAR(128);
  EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\MSSQLServer', 'ObjectName', @SN OUTPUT;  
  SELECT @SN;
  GO

  -- List SQL Server AGENT service account:
  DECLARE @SN NVARCHAR(128);
  EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT', 'ObjectName', @SN OUTPUT; 
  SELECT @SN;
  GO

  -- List SQL Server Integration Services service account: (SQL 2016 is MsDtsServer130)
  DECLARE @SN NVARCHAR(128);
  EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\MsDtsServer130', 'ObjectName', @SN OUTPUT; 
  SELECT @SN;
  GO

  -- List SQL Server Reporting Services service account:
  DECLARE @SN NVARCHAR(128);
  EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\ReportServer', 'ObjectName', @SN OUTPUT; 
  SELECT @SN;
  GO

  -- List SQL Server Analysis Services service account:
  DECLARE @SN NVARCHAR(128);
  EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\AnalysisServer', 'ObjectName', @SN OUTPUT; 
  SELECT @SN;
  GO

  -- List SQL Server MSDTC service account:
  DECLARE @SN NVARCHAR(128);
  EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\MSDTC', 'ObjectName', @SN OUTPUT; 
  SELECT @SN;
  GO

  BT