SQL Server by default follows Windows password policy requirements for SQL Server Login accounts. This forces you to follow the same policy for SQL Server logins as you would for windows logins and this is a good thing. But sometimes you want to bypass that validation maybe to get a test login in place. You can do this by using CHECK_POLICY = OFF. For example when I try to create a login using this statement I get an error.

CREATE LOGIN test_user WITH PASSWORD = 'password123'

Msg 15118, Level 16, State 1, Line 1

Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

The error itself tells me that my password does not meet policy requirements. The policy on my machine requires me to have at-least one upper case letter in my password. To bypass this requirement I can execute this statement which will create my SQL Login.

CREATE LOGIN test_user WITH PASSWORD = 'password123', CHECK_POLICY = OFF

You should however use this with caution because password policies are there for a reason i.e. to make your environment more secure.