[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Generate a password aging report for SQL 2005


I have a SQL 2005 server that is running in SQL Server and Windows Authentication Mode. Is there a way to generate a password aging report for all the SQL accounts on the server? Basically the report should say Username, How old the password is, Is account disabled or not.

2 Solutions
Unfortunately, the closest you could get is to find out the last time the any of the properties (default database, default language, etc.) for a login has changed.  SQL Server 2005 does not store a date that represents the last time the PWD was changed.

From mssqltips.com: "This value does not guarantee the date\time stamp of the password change, but rather any property change.  Since the properties do not change frequently under normal circumstances this value can serve as a reasonable, but not an absolute indicator of when the password was last changed.  If other properties have changed, the modify_date is not a true indicator and other research/documentation is needed to determine the last password change date."

SELECT name, is_disabled, modify_date FROM sys.server_principals WHERE type = 'S'

Your only other option would be to add a DDL trigger on your server to log every time someone changes their password.  Then you could use this table for your report.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now