Get SQL Login expiration date

Question

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

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

Answer 1

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.

Step Two: Check Window password policies using "secpol.msc".

The result is expected as below:

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.

Answer 2

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;

Answer 3

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

Answer 4

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;