SQL Server – Install Date – Application & Last Service Pack

Background

It is that time of year again for our Microsoft “True Up” and we need to come clean with new Installs.

 

What is “True Up”?

Link

 

Install & Last SP Date

Let us ask each database its install date and while at it get the date the last SP was applied.

Outline

  1. Install Date
    • Install
      • When SQL Service is installed some OS Accounts are automatically provisioned, as well
        • List of OS Accounts:
          • NT SERVICE\ReportServer
          • NT SERVICE\SQLSERVERAGENT
          • NT AUTHORITY\SYSTEM
          • NT Service\MSSQLSERVER
          • NT SERVICE\Winmgmt
          • NT SERVICE\SQLWriter
  2. Last Service Pack Apply Date
    • Service Pack Applied
      • When a Service Pack is applied, new certificates are applied, as well
        • ##MS_AgentSigningCertificate##
        • ##MS_PolicySigningCertificate##
        • ##MS_SQLAuthenticatorCertificate##
        • ##MS_SQLReplicationSigningCertificate##
        • ##MS_SQLResourceSigningCertificate##
        • ##MS_SmoExtendedSigningCertificate##

 

Code



set nocount on;
set transaction isolation level read uncommitted;
go
 
; with cteServerPrincipalNT
(
      [name]
    , [create_date]
)
as
(
 
    select
 
              tblSSP.[name]
            , tblSSP.[create_date]
 
    from   sys.server_principals tblSSP
 
    where   tblSSP.[name] in
                (
                      'NT SERVICE\ReportServer'
                    , 'NT SERVICE\SQLSERVERAGENT'
                    , 'NT AUTHORITY\SYSTEM'
                    , 'NT Service\MSSQLSERVER'
                    , 'NT SERVICE\Winmgmt'
                    , 'NT SERVICE\SQLWriter'
                )
 
)
, cteServerPrincipalCertificate
(
      [name]
    , [create_date]
)
as
(
 
    select
 
              tblSSP.[name]
            , tblSSP.[create_date]
 
    from   sys.server_principals tblSSP
 
    inner join master.sys.certificates tblSC
  
        on tblSSP.[name] = tblSC.[name]
  
)
 
select top 1
 
          [sqlInstance]
            = cast(serverproperty('servername') as sysname)
 
        , [installDate] 
            = convert(varchar(30), cteSP.[create_date], 100)
 
        , [servicePackDate] 
            = case

				when (
						   ( cteSPC.[create_date] > cteSP.[create_date] ) 
						or ( @@version not like '%RTM%') 
					) then
						convert(varchar(30), cteSPC.[create_date], 100)
						
				else null

			  end
 
from   cteServerPrincipalNT cteSP
 
outer apply cteServerPrincipalCertificate cteSPC
 
order by
          cteSP.[create_date] asc



Output