SQL Query to Get Login Password Expiry

Microsoft SQL ServerI 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

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) -
Your Email (required) -

Leave a Reply

Your email address will not be published. Required fields are marked *