-- SQL Diagnostic Manager v10.5.1.15  -- Copyright (c) IDERA, Inc. 2003-2020     
set
   transaction isolation level read uncommitted 
   set
      lock_timeout 20000 
      set
         implicit_transactions off if @@trancount > 0 commit transaction 
         set
            language us_english 
            set
               cursor_close_on_commit off 
               set
                  query_governor_cost_limit 0 
                  set
                     numeric_roundabort off 
                     select
                        name,
                        description,
                        minimum,
                        maximum,
                        config_value = value,
                        run_value = value_in_use,
                        restart_required = 
                        case
                           when
                              is_dynamic = 1 
                           then
                              0 
                           else
                              1 
                        end
, configuration_id 
                     from
                        sys.configurations 
                        declare @outputTable table (Name varchar(64), Value varchar(32));
declare @tempversion table (i int, Name nvarchar(100), Internal_Value bigint, Character_Value nvarchar(100));
-- Minimum support for Azure  
begin
   try 
   insert into
      @tempversion execute xp_msver 'WindowsVersion';
end
try 
begin
   catch print ERROR_MESSAGE() 
end
catch 
insert into
   @outputTable(Name, Value) 
   select
      'WindowsVersion',
      Character_Value 
   from
      @tempversion;
insert into
   @outputTable(Name, Value) 
   select
      'MinutesRunning',
      datediff(mi, create_date, getdate()) 
   from
      sys.databases 
   where
      name = 'tempdb';
insert into
   @outputTable(Name, Value) 
   select
      'EncryptedConnections',
      count(*) 
   from
      sys.dm_exec_connections 
   where
      encrypt_option = 'TRUE';
insert into
   @outputTable(Name, Value) 
   select
      'ServerProcessID',
      convert(varchar, serverproperty('ProcessID'));
insert into
   @outputTable(Name, Value) 
   select
      'TempDbRecoveryModel',
      convert(varchar, DATABASEPROPERTYEX('tempdb', 'RECOVERY'));
insert into
   @outputTable(Name, Value) 
   select
      'Edition',
      convert(varchar, SERVERPROPERTY('Edition'));
-- Minimum support for Azure  
begin
   try 
   insert into
      @outputTable(Name, Value) 
      select
         'PhysicalMemory',
         (
            physical_memory_kb * 1024
         )
      from
         sys.dm_os_sys_info;
end
try 
begin
   catch print ERROR_MESSAGE() 
end
catch 
insert into
   @outputTable(Name, Value) 
   SELECT
      'ProductVersion',
      CAST(SERVERPROPERTY('productversion') AS varchar(32));
-- Check SeManageVolumePrivilege  if 1 = (select cast(value_in_use as int) from sys.configurations where configuration_id = 16390) -- xp_cmdshell must be enabled   and 1 = IS_SRVROLEMEMBER('sysadmin') 
-- must be in sysadmin role or a proxy account is used to run commands  
begin
   declare @xp__cmdshell_output table(Output VARCHAR(8000)) 
   BEGIN
      TRY 		--insert into @xp__cmdshell_output exec ('xp__cmdshell ''whoami''');     
      insert into
         @outputTable(Name, Value) 
         select
            top 1 'SQLServerServiceAccount',
            rtrim(left([Output], 40)) 
         from
            @xp__cmdshell_output 
         where
            [Output] is not null 
   END
   TRY 
   BEGIN
      CATCH 
   END
   CATCH 
   BEGIN
      TRY 
      delete
      from
         @xp__cmdshell_output 			--insert into @xp__cmdshell_output exec ('xp__cmdshell ''whoami /priv''');     
         insert into
            @outputTable(Name, Value) 
            select
               'SeManageVolumePrivilege',
               ltrim(right([Output], 8)) 
            from
               @xp__cmdshell_output 
            where
               [Output] like 'SeManageVolumePrivilege %' if @@rowcount = 0 
               begin
                  if (not exists 
                  (
                     select
                        * 
                     from
                        @xp__cmdshell_output 
                     where
                        [Output] like ('% is not recognized as %')
                  )
) 
                  insert into
                     @outputTable(Name, Value) 
                  values
                     (
                        'SeManageVolumePrivilege',
                        'Disabled'
                     )
               end
   END
   TRY 
   BEGIN
      CATCH 
   END
   CATCH 
end
select
   Name,
   Value 
from
   @outputTable;