Generate a password aging report for SQL 2005

Posted on 2008-11-19
Last Modified: 2012-05-05

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.

Question by:PSBIT
    LVL 11

    Accepted Solution

    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 "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.
    LVL 31

    Assisted Solution

    by:James Murrell

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now