Tuesday, February 23, 2016

List of Users Who Recently Changed Their Password

There may come a time when you need to generate a list of SQL logins and the last time their password was changed. By using the LOGINPROPERTY of the name in sys.server_principals we can generate such a list.

Note: This will only work with SQL logins.

The following query will generate the results below.

SELECT  SP.name
            ,LOGINPROPERTY(SP.name,N'PasswordLastSetTime') 'LastPWReset'
FROM    sys.server_principals SP
WHERE   SP.[type] = 'S'