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 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] 
			= convert(varchar(30), cteSPC.[create_date], 100)

from   cteServerPrincipalNT cteSP

outer apply cteServerPrincipalCertificate cteSPC

order by 
		  cteSP.[create_date] asc



Output

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s