TRANSACT -SQL question

Question

Hello,

I have the below query which returns password expiration details for sql logins.

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

SELECT @@SERVERNAME AS ServerName, 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(int, LOGINPROPERTY(SL.name, 'PasswordLastSetTime'))) 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

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

If PasswordExpireChecked value is FALSE,

I want the values for DateUntilExpiration and PasswordExpirationDate set to 'NEVER EXPIRE'

Please help me

Thank you

Answer 1

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 2

Glad your problem is solved.
If you have any question, please feel free to let me know.

Regards
Echo

Answer 3

Glad your problem is solved.

If you have any question, please feel free to let me know.

Regards
Echo

Answer 4

Execute:

ALTER LOGIN YourLogin WITH CHECK_EXPIRATION=OFF;