Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » List all Logins in the server and change the...
Add to briefcase

List all Logins in the server and change the CHECK_POLICY=ON" Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jimmy.Liew
Posted Wednesday, August 11, 2010 1:43 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:50 AM
Points: 26, Visits: 308
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,

Jimmy
Post #967196
Schadenfreude-Mei
Posted Wednesday, August 11, 2010 3:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:40 AM
Points: 1,099, Visits: 1,320
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
Post #967245
Adiga
Posted Wednesday, August 11, 2010 8:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:23 PM
Points: 1,618, Visits: 20,901
Use ALTER LOGIN in conjunction with the above query to turn on CHECK POLICY

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #967479
WayneS
Posted Wednesday, August 11, 2010 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 6,370, Visits: 8,235
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #967522
Jimmy.Liew
Posted Wednesday, August 11, 2010 9:17 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:50 AM
Points: 26, Visits: 308
Hi,

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

Regards,

Jimmy
Post #967912
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.