How can I use TSQL to check when a user's password will expire?

Question

With SQL Server 2005 and above, how can I check when a user's password is going to expire using TSQL?

Preferably this would be done using a connection for that same user. What permissions would be required for the SQL statement to be run?

Answer 1

In SQL Server 2008, you can use SELECT LOGINPROPERTY('sa', 'DaysUntilExpiration').

For more accurate data - and also in SQL Server 2005, where DaysUntilExpiration is not supported, a combination of SELECT LOGINPROPERTY('sa', 'PasswordLastSetTime') and the password expiry policy out of Group Policy would do it.

According to SQL Server help, this function 'Requires VIEW permission on the login'.