Link to home
Create AccountLog in
Avatar of g_johnson
g_johnsonFlag for United States of America

asked on

SQL 2005 security / login issues

We have a SQL 2005 database set up to use the OS strong password policy.  When you violate the policy, e.g., login incorrectly 3 times, you are locked out.

Where does SQL store the fact that you are locked out?
Avatar of billycotx
billycotx
Flag of United States of America image

In the Server Manager Studio - Security - Login, double click on the user.
Login Properties - user name appears.
Select Status.
Under Status - there is a check box Login is locked out.
Avatar of chapmandew
alter login loginname with check_policy = off, enable
Avatar of g_johnson

ASKER

what I need to know is where it is actually, physically stored -- is it in a table that we can query?

thanks
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
thanks, chap.  What's interesting here, and what I can't figure out, is this query does return the user name, etc., but nowhere in sys.server_principals or any of the joined tables can you actually see a column that says "this user is locked."    This will solve our problem, and I'm considering this question as being answered, but we could get a better solution if we could put a trigger on a specific table.  It must be something in the LOGINPROPERTY class that is figuring this out.  I don't get it!  :-)

Thanks for your help.
No, you can't put triggers on system tables...

...what you might be able to pull off is a database level trigger or a server trigger that tracks when a user or login is altered.  Here are a couple of articles I wrote a long time ago that will show you how...

http://articles.techrepublic.com.com/5100-9592_11-6070262.html
http://articles.techrepublic.com.com/5100-9592_11-6072139.html

Otherwise, you're gonna have to use the query...not sure why MS did it that way, but that query is what I pulled from profiler when I looked to see if a user is locked out in management studio...so it is the same query MS uses to tell if an account is locked out...

HTH,
Tim
Thanks ...