If you are a SQLDBA, you must have created a login and you must have known about enforcing password policy to SQL Logins. But have you ever wondered how to find the expiry details about a SQL Login? In this blog post, we will explore different methods to retrieve this information.

Using LOGINPROPERTY Function

One way to find the expiry details of a SQL Login is by using the LOGINPROPERTY function. This function allows us to retrieve various properties of a login, including the password expiry details. Here is an example script that can be used:

SELECT  LOGINPROPERTY(name, 'BadPasswordCount') AS 'BadPasswordCount',
        LOGINPROPERTY(name, 'BadPasswordTime') AS 'BadPasswordTime',
        LOGINPROPERTY(name, 'DaysUntilExpiration') AS 'DaysUntilExpiration',
        LOGINPROPERTY(name, 'DefaultDatabase') AS 'DefaultDatabase',
        LOGINPROPERTY(name, 'DefaultLanguage') AS 'DefaultLanguage',
        LOGINPROPERTY(name, 'HistoryLength') AS 'HistoryLength',
        LOGINPROPERTY(name, 'IsExpired') AS 'IsExpired',
        LOGINPROPERTY(name, 'IsLocked') AS 'IsLocked',
        LOGINPROPERTY(name, 'IsMustChange') AS 'IsMustChange',
        LOGINPROPERTY(name, 'LockoutTime') AS 'LockoutTime',
        LOGINPROPERTY(name, 'PasswordHash') AS 'PasswordHash',
        LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'PasswordLastSetTime',
        LOGINPROPERTY(name, 'PasswordHashAlgorithm') AS 'PasswordHashAlgorithm',
        is_expiration_checked AS 'is_expiration_checked'
FROM    sys.sql_logins
WHERE   is_policy_checked = 1

This script will retrieve the expiry details for SQL Logins in SQL Server 2008 onwards.

Using Command Line

But what if you need to find the expiry details for Windows Logins? In such cases, you can use the command line to retrieve this information. Simply open a command prompt window and type the following command:

net user <User Name> /domain

The output of this command will display several lines of information. Look for the line that starts with “Password expires” to see the exact day and time when the account’s password will expire. If the account’s password does not have an expiry, it will display “Never”.

It’s important to note that this method is applicable for Windows Logins only.

Conclusion

In this blog post, we explored different methods to retrieve the expiry details of SQL Logins. We used the LOGINPROPERTY function to retrieve the details for SQL Logins and the command line to retrieve the details for Windows Logins. These methods can be helpful for SQLDBAs to manage and monitor the password expiry of logins in SQL Server.

Do you know any other way to retrieve the same information? Feel free to share your knowledge and let’s learn from each other.