Current SQL Authentication Method 

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');

1 = Windows only
0 = SQL & Windows
NULL = Type-o


tsql to show connections and their auth type

SELECT
    s.session_id
  , c.connect_time
  , s.login_time
  , s.login_name
  , c.protocol_type
  , c.auth_scheme
  , s.HOST_NAME
  , s.program_name
FROM sys.dm_exec_sessions s
  JOIN sys.dm_exec_connections c
    ON s.session_id = c.session_id
   
--SQL HTML email as a table

SQL HTML email as a table


create table #tmpChadRocksTable (
  SystemName NVARCHAR(255),
  SubjectName NVARCHAR(MAX),
  ExpirationDateString NVARCHAR(MAX),
  RD INT)


INSERT INTO #tmpChadRocksTable
exec chadrocks.dbo.AlertOnCerts

select * FROM #tmpChadRocksTable

DECLARE @sBody NVARCHAR(MAX)
DECLARE @Table NVARCHAR(MAX)


SET @sBody = CAST(( SELECT SystemName AS 'td','',SubjectName AS 'td','', ExpirationDateString AS 'td','', RD AS 'td',''
FROM  #tmpChadRocksTable
ORDER BY SystemName
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @Table ='
<table border = 1>
<tr>
<th>Name</th><th>NPI Number</th><th>CompletedDate</th></tr>'    
SET @sBody =  @Table +@sBody +'</table>'
print @sBody

DROP TABLE #tmpChadRocksTable

Alert for getting a more recent version of a Cert

ALTER PROCEDURE [dbo].[AlertOnCerts](
   @iLookAheadInDays INT = 45

  )

  AS
;WITH CTE AS
    (
    SELECT DISTINCT SystemName, [SubjectName],ExpirationDateString
       ,RN=ROW_NUMBER() OVER (PARTITION BY SystemName, [SubjectName] ORDER BY ExpirationDateString DESC) FROM [ChadRocks].[dbo].[Certificates] c
      JOIN [ChadRocks].[dbo].[SystemCertificates] sc
     ON c.id=sc.CertID
      JOIN [ChadRocks].[dbo].[System] s
        ON sc.SystemID=s.SystemID
    WHERE    
    CONVERT(datetime,ExpirationDateString,110) >  DATEADD(DAY,-1,CONVERT(datetime,GETDATE(),110))
     AND
       CONVERT(datetime,ExpirationDateString,110) < dateadd(day,@iLookAheadInDays,CONVERT(datetime,GETDATE(),110))
   
    )
    select * from CTE where RN > 0 ORDER BY SystemName