Obtain SQL Login Password Related Information

Populaire vragen (FAQ's) zoeken

Issue
How to list SQL login password related information from the SQL database.

Product Line
Struxureware Power Monitoring 7.0.x
Power Monitoring Expert 7.2.x, 8.x, 9.x
ION Enterprise 6.0. x
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R 2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017

Cause
In order to troubleshoot and validate SQL login issues, it may be necessary to ensure that the account and credentials being
used to access the database are in fact valid and have not been disabled or locked out.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio.
Databases should be backed up prior to performing this procedure.*

With this SQL statement you can query this information for all SQL logins and calculate the date for password.
This will also return login information such as the last time the password has been change, how many days it take till the password expires,
how often a bad password was entered or if the login is already locked.

To obtain this list, log into SQL Server Management Studio and run the attached SQL script (LoginPassword.sql).
Alternatively, execute the query(s) below:

SELECT SL.name AS LoginName ,LOGINPROPERTY (SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime ,LOGINPROPERTY (SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration ,DATEADD(dd, CONVERT(int, LOGINPROPERTY (SL.name, 'DaysUntilExpiration')) , CONVERT(datetime, LOGINPROPERTY (SL.name, 'PasswordLastSetTime'))) AS PasswordExpiration ,SL.is_policy_checked AS IsPolicyChecked ,LOGINPROPERTY (SL.name, 'IsExpired') AS IsExpired ,LOGINPROPERTY (SL.name, 'IsMustChange') AS IsMustChange ,LOGINPROPERTY (SL.name, 'IsLocked') AS IsLocked ,LOGINPROPERTY (SL.name, 'LockoutTime') AS LockoutTime ,LOGINPROPERTY (SL.name, 'BadPasswordCount') AS BadPasswordCount ,LOGINPROPERTY (SL.name, 'BadPasswordTime') AS BadPasswordTime ,LOGINPROPERTY (SL.name, 'HistoryLength') AS HistoryLength FROM sys.sql_logins AS SL WHERE is_expiration_checked = 1 ORDER BY LOGINPROPERTY (SL.name, 'PasswordLastSetTime') DESC