Locking User account created under Windows Authentication in SQL Server

Question

Hi,

As per my project requirement i need to lock a user in SQL Server(Which is created using Windows Authentication). Is there any way to do this?

For example: For a SQL login if you try to login using wrong Password more than 3 or 4 attempts, then that account gets locked out. User cannot login to SQL Server using this username. I want to check this using Users created under windows authenntication

Thanks for the help

Santhosh

Answer 1

You need to keep two things apart:

• on a server-level, you have users that have a login - this gives them the ability to connect to that SQL Server at all. You can disable a login using:

  ALTER LOGIN (name) DISABLE
  

  Doing so prevents that user from logging into the database server alltogether - he (or she) cannot access anything on that database server anymore

• on a per-database level, those logins might be granted access to the database - this is done by creating a user (based on that login) for that database, and assigning that user certain permissions. You can't really disable a user in a database - you just have to drop that user

  USE (database)
  DROP USER (name)
  

  You can always re-create that user from his login in that database using

  USE (database)
  CREATE USER (name) WITH LOGIN = (login name)