Get SQL Login expiration date

rabinda77 66 Reputation points
2021-02-24T02:05:44.047+00:00

I am running the following query,

When I run the query values I am getting for DaysUntilExpiration and PasswordExpirationDate are not matching.
For example it shows 40 DaysUntilExpiration and PasswordExpirationDate as 03/16/2021

Can you please help me to resolve it..

Thank you

=================================================================

SELECT @@SERVERNAME AS ServerName, SL.name AS LoginName
,LOGINPROPERTY(SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime
,ISNULL(CONVERT(varchar(100),LOGINPROPERTY(SL.name, 'DaysUntilExpiration')),'Never Expire') AS DaysUntilExpiration
,ISNULL(CONVERT(varchar(100),DATEADD(dd, CONVERT(int, LOGINPROPERTY(SL.name, 'DaysUntilExpiration')),CONVERT(int, LOGINPROPERTY(SL.name, 'PasswordLastSetTime'))),101),'Never Expire') AS PasswordExpirationDate,

CASE
WHEN is_expiration_checked = 1 THEN 'TRUE' ELSE 'FALSE'
END AS PasswordExpireChecked

FROM sys.sql_logins AS SL

WHERE SL.name NOT LIKE '##%' AND SL.name NOT LIKE 'endPointUser' and is_disabled = 0

ORDER BY (LOGINPROPERTY(SL.name, 'PasswordLastSetTime')) DESC

================================================================

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-24T02:54:35.547+00:00

    Hi @rabinda77 ,

    I tried to create a new login with default 42 days password expiration and executed your query with matching result.

    Step One: Set the login properties in SSMS.
    71401-newlogin.png

    Step Two: Check Window password policies using "secpol.msc".
    71383-policy.png

    The result is expected as below:
    71377-query.png

    Please have a try to check above two steps and re-created this login if necessary since your query is working. Thanks.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


3 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2021-02-24T02:50:48.09+00:00
    SELECT 
      @@SERVERNAME AS ServerName
    , name AS LoginName
    , LOGINPROPERTY(name, 'PasswordLastSetTime') AS PasswordLastSetTime
    , ISNULL(LOGINPROPERTY(name, 'DaysUntilExpiration'), 'Never Expire') AS DaysUntilExpiration
    , ISNULL(CONVERT(VARCHAR(10), DATEADD(DAY, CONVERT(int, LOGINPROPERTY(name, 'DaysUntilExpiration')), CONVERT(DATE, LOGINPROPERTY(name, 'PasswordLastSetTime'))), 101), 'Never Expire') AS PasswordExpirationDate
    , CASE WHEN is_expiration_checked = 1 THEN 'TRUE' ELSE 'FALSE' END AS PasswordExpireChecked
    FROM sys.sql_logins
    ORDER BY PasswordLastSetTime DESC;
    
    1 person found this answer helpful.
    0 comments No comments

  2. Arron 1 Reputation point
    2022-09-20T14:59:22.297+00:00

    Hi,

    Queries above seem wrong, I'm getting different values for DaysUntilExpiration and PasswordExpirationDate daily.
    DaysUntilExpiration is number of days from now, not from PasswordLastSetTime.
    Our password expiration policy is 90 days, DaysUntilExpiration was 89 day after I created the SQL logins, now 40 days later DaysUntilExpiration is 49.

    This seems correct for PasswordExpirationDate doing the dateadd against getdate():

    SELECT @@SERVERNAME AS ServerName, SL.name AS LoginName
    ,LOGINPROPERTY(SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime
    ,ISNULL(CONVERT(varchar(100),LOGINPROPERTY(SL.name, 'DaysUntilExpiration')),'Never Expire') AS DaysUntilExpiration
    ,ISNULL(CONVERT(varchar(100),DATEADD(dd, CONVERT(int, LOGINPROPERTY(SL.name, 'DaysUntilExpiration')),convert(int, getdate())),120),'Never Expire') AS PasswordExpirationDate,
    CASE
    WHEN is_expiration_checked = 1 THEN 'TRUE' ELSE 'FALSE'
    END AS PasswordExpireChecked
    FROM master.sys.sql_logins AS SL
    WHERE SL.name NOT LIKE '##%' AND SL.name NOT LIKE 'endPointUser' and is_disabled = 0
    ORDER BY (LOGINPROPERTY(SL.name, 'PasswordLastSetTime')) DESC

    0 comments No comments

  3. Rakesh Rayabarapu 0 Reputation points
    2023-11-30T14:00:08.75+00:00

    Hi,

    Further updated with password expiry frequency and increased accuracy on expiration date including time.

    SELECT @@SERVERNAME AS ServerName, name AS LoginName

    ,LOGINPROPERTY(name, 'PasswordLastSetTime') AS PasswordLastSetTime

    ,ISNULL(CONVERT(varchar(100),LOGINPROPERTY(name, 'DaysUntilExpiration'))+' Days','Never Expire') AS ExpireAfter

    ,ISNULL(CONVERT(varchar(100),DATEADD(day,CONVERT(int, LOGINPROPERTY(name, 'DaysUntilExpiration'))+ DATEDIFF_BIG(day, CONVERT(datetime,LOGINPROPERTY(name, 'PasswordLastSetTime')),GETDATE()),CONVERT(datetime,LOGINPROPERTY(name, 'PasswordLastSetTime'))),120),'Never Expire') AS PasswordExpirationDate,

    ISNULL(CONVERT(varchar(100),CONVERT(int, LOGINPROPERTY(name, 'DaysUntilExpiration'))+ DATEDIFF_BIG(day, CONVERT(datetime,LOGINPROPERTY(name, 'PasswordLastSetTime')),GETDATE()))+ ' Days','Never Expire') AS ExpiresEvery,

    CASE

    WHEN is_expiration_checked = 1 THEN 'TRUE' ELSE 'FALSE'

    END AS PasswordExpireChecked

    FROM master.sys.sql_logins

    WHERE name NOT LIKE '##%' AND name NOT LIKE 'endPointUser' and is_disabled = 0;

    0 comments No comments