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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.