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'

SELECT	
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
GO

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'

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

 So, give LOGINPROPERTY a chance!

CU