Generate a password aging report for SQL 2005

Hello:

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.

Thanks
PSBITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aaronakinCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.