Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

List all Logins in the server and change the CHECK_POLICY=ON" Expand / Collapse
Author
Message
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: Saturday, March 22, 2014 7:08 AM
Points: 27, Visits: 329
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
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: Tuesday, March 4, 2014 10:03 AM
Points: 1,106, Visits: 1,334
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
Posted Wednesday, August 11, 2010 8:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 1,618, Visits: 20,980
Use ALTER LOGIN in conjunction with the above query to turn on CHECK POLICY

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 6,582, Visits: 8,864
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
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: Saturday, March 22, 2014 7:08 AM
Points: 27, Visits: 329
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