How to set CHECK_POLICY OFF in the sql script

Question

While installing our software in a HP laptop, we got a SQL error stating "The password does not meet windows policy requirements because it is too short."

When I checked, the local security policy in my machine has minimum 8 characters and in that laptop it has 12 characters. Our SQL password has 11 characters. That is why it is not installed in that laptop alone.

We can increase the password more than 12. But maybe in future the minimum password requirement can even change to 20 characters. So we thought of disabling the password check. Since I am new to this SQL, I don't know where to add the condition check. I have two SQL scripts. The code snippet for the 2 scripts is shown below. Please let me know where to add it.

Script1:

ALTER LOGIN [sa] WITH PASSWORD=N'MSSql2008!' GO
IF EXISTS (SELECT * FROM syslogins WHERE name = 'teradyne') 
 BEGIN ALTER LOGIN [teradyne] WITH PASSWORD=N'SQL_PWD' END
GO

Script2:

GO
EXEC ('IF NOT EXISTS (SELECT * FROM syslogins WHERE name = ''clientsoftware'') 
 EXEC sp_addlogin @loginame=''clientsoftware'', @passwd=''TER_SQL_PWD'' 
 ') GO
CHECKPOINT
GO

Where SQL_PWD = software1! for both scripts.

Please let me know where to add that CHECK_POLICY and also whether I need to add that CHECK_EXPIRATION

• Mikael Eriksson: Duplicate on Stack Overflow

Answer 1

You have to do

Alter login login_name with CHECK_POLICY = { ON | OFF }
Alter login login_name with CHECK_EXPIRATION = { ON | OFF }

Refer: http://technet.microsoft.com/en-us/library/ms189828.aspx

• StackUser: Thanks. For Alter login we can use CHECK_POLICY. But for sp_addlogin?
• Kin Shah: @user1168494 sp_addlogin - will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE LOGIN instead.