Showing posts with label why create date in server_principals. Show all posts
Showing posts with label why create date in server_principals. Show all posts

Sunday, August 30, 2020

Server Principals Login Create and Modification Date

The certificate-based logins that start and end with double hash marks are stated below -

##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_PolicySigningCertificate##
##MS_SmoExtendedSigningCertificate##
##MS_AgentSigningCertificate##

By default, they are Windows-based logins and are enabled. If you want to look into both windows and SQL based logins, query against sys.server_principals as mentioned below.

USE master
GO
SELECT * FROM sys.server_principals
WHERE  name LIKE '%##%'


If you look into sys.server_principals for the certificate-based logins, you might confuse with the "create date" and "modify date" of those logins. The logins were created when SQL Server is installed however the dates are different than the installation date.

These logins will be dropped and recreated during the 'script level upgrade' which usually takes place after cumulative updates/patches or an unexpected restart of the active SQL node.

There won't be any change in the dates (since no logins will be recreated) when the services are restarted or routine failover takes place.


If you need more information on Server Principals, please click here.




Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...