23 thoughts on “SQL SERVER – Disable Windows Authentication – Remove Windows Authentication Login Account

  1. Technically by disallowing Windows users access you can block anyone’s connection in effect disabling it.

    While being the most secure, its not the fastest…for sites that have hundreds/thousands of connections made per minute there is a noticable difference between Windows and SQL Server Authentication.

    Like

  2. How to implement database clustering in SQL Server 2005..Kindly let me know the steps for it…Does it really benefits the organisation….

    Like

  3. When I open ALM application (Automation license managment) I get error ” Login in database is momentarily out of service”

    How can I remove this warning and start application normally.

    As above it is said “disallowing Windows users access you can block anyone’s connection in effect disabling it.”

    how can we do it

    Like

  4. Hello Sir,

    Can you tell me how can I protect my database with password. I do not want user to operate on database without password.

    Is there any way to disable Windows Authentication or any way to protect database with password.

    I think there should be some provision to protect database.

    I have database on Win XP system and there’s no domain system at my client side. I just want to protect my database. I dont want anybody to view data without my permission.

    Thanks a lot for lots of previous posts.
    I hope you will give me a ultimate solution for this too..

    Thankx again.

    Like

  5. Hello Ishrar,

    Use “SQL Server and Windows Authentication mode” as server authentication. Then do not create any login for any windows user. You can also remove Builtin\Administrator group.
    This way you there would be only SQL logins though which user would login in your database.

    Regards,
    Pinal Dave

    Like

    • Pinal Dave,
      I note your comment of March 29 and I have been trying to remove the BUILTIN\Administrators and the BUILTIN\Users groups but no success. I have tried both DROP LOGIN and ALTER LOGIN … DISABLE but I get a syntax error message. Using SQL Server Express 2008.
      Can you help?

      Jeremy

      Like

    • Thanks Pinal.. Your blog always helps me.. You Replied me 4 years back. Today again I need same thing to be done using Command.. I want to remove Windows Authentication using Command.. The reason is, My clients have their databases on their server but I dont want them to access my Application’s database. I use to create new User XYZ with password which is known by my application only. And I use to remove Windows Authentication From SSMS. Sometimes I dont have SSMS installed, And I don’t want to install (for security purpose). How can I remove Windows Authentication using Command.
      Thanks again…

      I am very confused in Database Backup restore because when I restore from SSMS choosing which database I am replacing, why still I have to mention which file should be restored.. Actually do replace the file name but I must know the reason how and why it works like this.

      Like

  6. Regarding SQL Server security, We have a 3rd party app, Costpoint from Deltek, which users login to access and update our 2005 SQL Server database. I’ve been tasked to disable an account after 3 invalid login attempts through this application to the database. My boss doesn’t want me to go the route of the alter login command on the database side, and I’m not allowed to update the application code either. Is there a way to do this using SQL Server authentication?

    Like

  7. to disable window authentication login, you must collapse the “Security>Server Roles” part under the list of your databases. Right Click the ‘public’ role and select properties. select ‘Permissions’ under ‘General’ and then tick the ‘Deny’ CheckBox for the ‘Connect’ permission.
    All users using window authentication only that would try to connect to the server would be blocked because BUILTIN\USERS have public permissions only.

    Regards,

    Ross

    Like

  8. How to protect database, by login through static IP from out side the network using sQL management studio. But application should be run from that location. Application is accessing database

    Like

  9. USE [master]
    GO
    ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
    GO
    EXEC master..sp_dropsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
    GO

    Login with SA and run above query to disable SQL SERVER – Windows Authentication.

    Like

  10. Hi Pinal,

    I changed my xp machine password and since than My sql server express (2008) windows authentication is not working through .net.

    I created one udl and test windows authentication. test is fine but its shows me only three databases Master, MsDB and TempDB.

    How can i Fix this?

    Regards,

    Saily

    Like

  11. Hello Pinal Dave
    in MS Access , if you secure the db with password , you can’t open this db without password even if you copy this db to another PC , or you can’t open it with windows authentication !!

    Is there way to secure my db in SQL Server with password and no one can open it even if he copy it to another PC or using windows authentication ??

    Like

  12. Open SQL Management Studio. select SQL authentication mode and logging (which have full credential rights). in Object Explorer, Security->Login. Right click on [BUILTIN\Users] and select “propeties”. go to “Status” and select “disable” login. find your windows authentication users and apply job same as above.

    It works for me. now I can only connect with SQL server authentication mode.

    Like

  13. Hai Sir,
    I am using Sql Server 2008 R2 version,I have some problem in that,please clear my problem.I created one database in the “server authentication” mode,then i save it.if i disconnect the “server authentication” and go to “windows authentication” and open means i can able o see that database an table(that is my problem).
    i want solution for this(i can create database in “server authentication” mode but in “windows authentication” no one see that)(if any option to do this)..
    Regards,
    kutti

    Like

  14. Hi Pinal,Hi, Am quite new to SQL Server. I see that SQL Server has 3 auth modes :

    1. SQL Server login credentials

    2. Windows Auth

    3. Mixed (1 or 2)

    Which is more commonly used in today’s world in firms?

    Like

  15. Sir,
    Is there any way to know the list of all inactive Windows users in the AD thru the SQL query ?

    I want to list out all the users(Windows) who has the access level permission on the SQL Server but they can’t enter into the SQL Server as they are blocked or their account disabled in OS Level.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s