Set SQL login policy on all accounts script

Can someone help with a script to change all the SQL authenticated logins account on SQL server? I know there's ALTER LOGIN ' ' WITH CHECK_POLICY = OFF, but I want to change all the SQL authenticated accounts.

Thanks
barnescoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use this script:
USE master

DECLARE @Command NVARCHAR(128)

DECLARE Logins CURSOR FOR
	SELECT loginname
	FROM sys.syslogins
	WHERE isntname=0 and loginname NOT LIKE '#%'

OPEN Logins
FETCH NEXT FROM Logins INTO @LoginName

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Command = 'ALTER LOGIN ' + @LoginName + ' WITH CHECK_POLICY = OFF'
		
		EXEC sp_executesql @Command
		FETCH NEXT FROM Logins INTO @LoginName
	END

CLOSE Logins 
DEALLOCATE Logins

Open in new window

0
barnescoAuthor Commented:
Received error:

Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@LoginName".
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@LoginName".
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@LoginName".
0
barnescoAuthor Commented:
Declaring @LoginName as NVARCHAR(128) didn't help either:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CHECK_POLICY'.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:
Press Ctrl_T before running if you want to see each ALTER command on a separate line, otherwise the commands will wrap around continuously.  That won't affect how they run, just how they look to you when you they are shown back to you:


DECLARE @Command nvarchar(max)
SET @Command = ''

SELECT @Command = (
    SELECT CAST(CHAR(10) AS nvarchar(max)) +
        'ALTER LOGIN [' + name + '] WITH CHECK_POLICY = OFF;'
    FROM sys.sql_logins
    WHERE
        name NOT LIKE '#%' AND
        is_policy_checked = 1 AND
        type = 'S'
    ORDER BY name
    FOR XML PATH('')
    )

SELECT @Command
EXEC(@Command)
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
In the Declare Cursor statement it says

SELECT Loginname

It should say
SELECT name
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, forgot to copy the declaration of @LoginName:
USE master

DECLARE @LoginName NVARCHAR(128)
DECLARE @Command NVARCHAR(128)

DECLARE Logins CURSOR FOR
	SELECT loginname
	FROM sys.syslogins
	WHERE isntname=0 and loginname NOT LIKE '#%'

OPEN Logins
FETCH NEXT FROM Logins INTO @LoginName

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Command = 'ALTER LOGIN ' + @LoginName + ' WITH CHECK_POLICY = OFF'
		
		EXEC sp_executesql @Command
		FETCH NEXT FROM Logins INTO @LoginName
	END

CLOSE Logins 
DEALLOCATE Logins

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
The view "sys.syslogins" is ancient (from SQL 2000) and deprecated.  You really should quit using it, esp. in new code.
0
barnescoAuthor Commented:
Worked great. Thanks for the code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.