Login Password Policy

,

Outputs a list of all the SQL logins and for each one indicates if the password policy in NOT enforced or if the password IS enforced, is the password expiration also enforced.

SET NOCOUNT ON

SELECT 
    name, 
    CASE CAST(is_policy_checked AS TINYINT) + CAST(is_expiration_checked AS TINYINT)
        WHEN 0 THEN 'Not Enforced'
        WHEN 1 THEN 'Password - No Expiration'
        WHEN 2 THEN 'Password With Expiration'
     END AS PasswordEnforcement ,
    LOGINPROPERTY(name,'BadPasswordCount') AS BadPasswordCount,
    LOGINPROPERTY(name,'BadPasswordTime') AS BadPasswordTime,
    LOGINPROPERTY(name,'DaysUntilExpiration') AS DaysUntilExpiration,
    default_database_name,
    CASE WHEN LOGINPROPERTY(name,'IsExpired') = 0 THEN 'NO' ELSE 'YES' END AS IsExpired,
    CASE WHEN LOGINPROPERTY(name,'IsLocked') = 0 THEN 'NO' ELSE 'YES' END AS IsLocked,
    CASE WHEN LOGINPROPERTY(name,'IsMustChange') = 0 THEN 'NO' ELSE 'YES' END AS IsMustChange,
    LOGINPROPERTY(name,'LockoutTime') AS LockoutTime,
    LOGINPROPERTY(name,'PasswordLastSetTime') AS PasswordLastSetTime
FROM sys.sql_logins
ORDER BY name

Rate

4 (1)

Share

Share

Rate

4 (1)