SQL Server 2017 Administrator's Guide

Managing login properties

Many logins could have been created without the policy and expiration checks, so if you would like to find them all, you can use the following query to list all SQL Server types of login, where the checks are not in place:

SELECT serverproperty('machinename') as 'Server Name', [name], [is_policy_checked], [is_expiration_checked] FROM master.sys.sql_logins WHERE ( [is_policy_checked] = 0 OR [is_expiration_checked] = 0 ) and name not like '##MS_%'

One more option that you can select while creating a SQL Server type of login is to force the login to change the password during the next login, which is useful when the SQL login is utilized by a developer or administrator, but not particularly useful if the login is used ...