SQL Query to Get Login Password Expiry

I don’t recall exactly why this script was required, but the below SQL query can be used to get the expiration date for logins in Microsoft SQL Server. Some of the data is available directly from the sys.sql_logins table, but other pieces had to be retrieved using the LOGINPROPERTY function:

SELECT

	[‘SQL Logins’].name AS ‘LoginName’

	,LOGINPROPERTY([‘SQL Logins’].name, ‘PasswordLastSetTime’) AS ‘PasswordLastSetTime’

	,LOGINPROPERTY([‘SQL Logins’].name, ‘DaysUntilExpiration’) AS ‘DaysUntilExpiration’

	,DATEADD(dd,CONVERT(int, LOGINPROPERTY ([‘SQL Logins’].name, ‘DaysUntilExpiration’)),CONVERT(datetime,LOGINPROPERTY([‘SQL Logins’].name,‘PasswordLastSetTime’))) AS ‘PasswordExpiration’

	,[‘SQL Logins’].is_policy_checked AS ‘IsPolicyChecked’

	,LOGINPROPERTY([‘SQL Logins’].name, ‘IsExpired”) AS ‘IsExpired’

	,LOGINPROPERTY([‘SQL Logins’].name, ‘IsMustChange”) AS ‘IsMustChange’

	,LOGINPROPERTY([‘SQL Logins’].name, ‘IsLocked”) AS ‘IsLocked’

	,LOGINPROPERTY([‘SQL Logins’].name, ‘LockoutTime”) AS ‘LockoutTime’

	,LOGINPROPERTY([‘SQL Logins’].name, ‘BadPasswordCount”) AS ‘BadPasswordCount’

	,LOGINPROPERTY([‘SQL Logins’].name, ‘BadPasswordTime”) AS ‘BadPasswordTime’

	,LOGINPROPERTY([‘SQL Logins’].name, ‘HistoryLength’‘) AS ‘HistoryLength’

FROM

	sys.sql_logins AS [‘SQL Logins’]

WHERE

	is_expiration_checked = 1

ORDER BY

	[‘SQL Logins’].name

Read original post SQL Query to Get Login Password Expiry at azurecurve|Ramblings of a Dynamics GP Consultant