Determine the last change of the sa password?

Let us face it, when did you last change the sa password?

You can check last change in SQL Server 2005, for the sa login by using LOGINPROPERTY function.

Execute the following T-SQL code:

SELECT LOGINPROPERTY ('sa', 'PasswordLastSetTime')

And a little bit more...

DECLARE @name nchar(100)
SET @name ='sa'

LOGINPROPERTY( @name, 'PasswordLastSetTime' )
	AS PasswordLastSetTime,
LOGINPROPERTY( @name, 'IsExpired' )
	AS IsExpiried,
LOGINPROPERTY( @name, 'IsLocked' )
	AS IsLocked,
LOGINPROPERTY( @name, 'IsMustChange' )
	AS IsMustChange,
LOGINPROPERTY( @name, 'LockoutTime' )
	AS LockoutTime,
LOGINPROPERTY( @name, 'BadPasswordCount' )
	AS BadPasswordCount,
LOGINPROPERTY( @name, 'BadPasswordTime' )
	AS BadPasswordTime,
LOGINPROPERTY( @name, 'HistoryLength' )
	AS HistoryLength,
LOGINPROPERTY( @name, 'PasswordHash' )
	AS PasswordHash

LOGINPROPERTY comes in useful for several other bits of information about login policy settings in SQL Server 2005:

  • Check if login is locked,expired
  • Check if password must be changed
  • Check bad password attempts, and time since last bad attempt
  • Check the length of time the login has been password-policy enforced
  • Check lockout time
  • ...

And now what about Microsoft SQL Server 2008 / R2 are there any changes?
Yes, indeed!

We get LOGINPROPERTY with three new arguments:

  • DaysUntilExpiration
  • DefaultDatabase
  • DefaultLanguage
DECLARE @name nchar(100)
SET @name ='sa'

LOGINPROPERTY( @name, 'PasswordLastSetTime' )
	AS PasswordLastSetTime,
LOGINPROPERTY (@name, 'DaysUntilExpiration' )
	AS DaysUntilExpiration,
LOGINPROPERTY (@name, 'DefaultDatabase' )
	AS DefaultDatabase,	
LOGINPROPERTY (@name, 'DefaultLanguage' )
	AS DefaultLanguage	

 So, give LOGINPROPERTY a chance!


tosc - Sapere aude!