User Password Age

A query that lists every enabled SQL login whose password was last changed more than 90 days ago.

Published Feb 23, 2006

A quick compliance check. This query uses LOGINPROPERTY to pull the PasswordLastSetTime for every non-disabled SQL login and returns only the ones older than 90 days - change the DATEADD interval at the bottom to match your password policy.

-- Show all logins where the password is over 90 days old
SELECT
	name,
	LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM
	sys.sql_logins
WHERE
	is_disabled = 0
	AND LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -90, GETDATE())
ORDER BY
	name