List all Logins in the server and change the CHECK_POLICY=ON"

Question

Post reply

List all Logins in the server and change the CHECK_POLICY=ON"

monkeyDBA

Default port

Points: 1457
More actions
August 11, 2010 at 1:43 am

#142349

Hi all,
Can anyone like to share how to list all the logins from syslogins system table, later change all the "CHECK_POLICY=ON" if it is "OFF".
Kindly advice. Thanks.
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

This hould give you all SQL accounts (so no domain or built-in accounts)

And then you will have to write a script to wrap around each account and turn policy either on or off.

SELECT name, loginname FROM syslogins WHERE name NOT LIKE '%\%' AND name NOT LIKE '#%'

Adam Zacks-------------------------------------------Be Nice, Or Leave

Adiga One Orange Chip Points: 27284 More actions · Answer 2

Use ALTER LOGIN in conjunction with the above query to turn on CHECK POLICY

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

WayneS SSC Guru Points: 95409 More actions · Answer 3

All the pieces from above, all put together.

SELECT 'ALTER LOGIN ' + QuoteName(name) + ' WITH CHECK_POLICY = ON;'

FROM sys.server_principals

WHERE [type] = 'S' -- SQL Server Logins only

AND principal_id > 1 -- not sa - omit this line if you want to include it

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

monkeyDBA Default port Points: 1457 More actions · Answer 4

Hi,

You guys are really helpful. I really appreciate your reply on this, it save me a lot of time. Thanks. 😀

Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.