At what point is SQL Server's is_policy_checked enforced? At what point is SQL Server's is_policy_checked enforced - just at next password change or at each login?
The CHECK_POLICY option is used to enforce Windows Password Policy restrictions and mechanisms for SQL Server logins. This applies when a login is created or when a password is changed for a SQL Server login as it affects the validity of a chosen password based on the setting state and the Windows password policy. It does not perform any validation or checks when a user logs on.

If you're trying to force passwords to be validated after enabling this option on existing logins, you should also enable the MUST_CHANGE option to force users to change their password on next login as the new passwords would then have to comply with the new policy.