Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

My old employee has disabled Windows Authentication in our server. Now I'm not able to access the SQL Server even though I have Administrator access to the server. I need to reset the sa password.

I tried logging in using single user mode as Remus described but I get the following error:

Login failed for user 'SERVER\Administrator'.
Reason: The account is disabled.
(Microsoft SQL Server, Error: 18470)

share|improve this question

migrated from May 17 '13 at 13:13

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Paul White Sep 4 at 11:13

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

@aaron Thanks. Learn something new everyday. Question, I did some test and if NT AUTHORITY\SYSTEM SQL login is also disabled/deleted, is there still any way around? –  Travis Gan May 17 '13 at 15:24
@Travis not with PSEXEC I don't think; you'll need to use a different workaround. –  Aaron Bertrand May 17 '13 at 15:25
@aaron OK, at least we know the MDF/LDF files copy solution to make sure nobody get screwed. –  Travis Gan May 17 '13 at 15:29
@AkhilKNambiar There is one more option that I can think of. After querying sys.server_principals joined with sys.server_role_members I realized that there are 2 more sysadmins in my server: the accounts used by the services MSSQLSERVER and SQLSERVERAGENT. You can use services.msc or the configuration manager to find what user name your services are using, and then find a way to impersonate them. –  Roi Gavish May 19 '13 at 16:57

4 Answers 4

Follow the steps in:

Connect to SQL Server When System Administrators Are Locked Out.


  1. Start server with the -m switch
  2. Connect as a local administrator
  3. Enable the sa account (and reset its password if necessary)
  4. Restart server normally
share|improve this answer

Based on feedback in the comments on this answer, the situation is this:

  • There is an explicitly-created BUILTIN\Administrators group login in SQL Server that has been denied CONNECT to the database engine.

  • There is no other sysadmin-level login available, including the NT AUTHORITY\SYSTEM login that gets created by default.

SQL Server does not allow a user to disable the BUILTIN\Administrators login, but it can still be denied CONNECT. I consider this a bug, as it's obvious that the inability to disable the login outright was hard-coded into the engine to avoid scenarios like this.

I'm unable to get in after applying that permission.

You will have to either find a way to edit the contents of the master database to get rid of the DENY permission (totally unsupported, and at your own peril), reinstall the database engine from the installation media, or I suppose you could attempt to brute-force the sa password.

I don't believe you can simply rebuild master as that requires connecting and authenticating against the database engine, which isn't available here.

share|improve this answer
Logged in as newly created admin but unable to access sql account since BuilIn Users/Administrators doesn't have access to MSSQL Server. –  Akhil K Nambiar May 17 '13 at 14:00
Lovely. Okay, I'll have to work on that. Did you try one of the methods on my blog? If someone has gone to the extent of locking out BUILTIN\Administrators (bad idea, by the way), the system account login is probably gone, too, but it's worth a shot. –  Jon Seigel May 17 '13 at 14:23
ya. can't connect login in anyway... Login failed for user 'SERVER\Administrator'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470) –  Akhil K Nambiar May 17 '13 at 14:52
This is interesting. For testing, I added a BUILTIN\Administrators login to my instance, which succeeded. I tried to disable it, but it wouldn't let me. I was able to DENY CONNECT, however, and this gives me the same error message. Now... to try and fix it. –  Jon Seigel May 17 '13 at 15:06

When all windows user accounts with sysadmin role or sa account are disabled in SQL server then how to access the instance?

Below are the steps to solve this problem:

  1. Stop existing instance of SQL Server on which all windows user accounts or sa account are disabled

  2. Start SQL Server instance in single user mode with following steps

    2.1 Open command prompt in admin mode

    2.2 Run command ( I am running SQL Server 2012 instance)

    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -s 
    SQL2012 –m
  3. Open another command prompt in admin mode

  4. Connect to the SQL Server instance using command

    C:>sqlcmd –S(local)\instancename –E
  5. Create login

    C:> CREATE LOGIN [loginname] FROM WINDOWS;
  6. Add login to sysadmin role

    C:> ALTER SERVER ROLE sysadmin ADD MEMBER [loginname]
  7. Now disconnect from SQL Server instance

  8. Stop SQL Server instance by pressing cntl+C and ‘Y’

  9. Start SQL Server instance in multi-user mode and now you will be able to connect using newly created login

share|improve this answer

I'm going to second the service account suggestion made by Justicator. If that doesn't work...

This is a long shot, but are you able to do something like this?

(It would depend on NT Authority\System, which may be blocked by the BUILTIN\Administrators explicit deny.)

share|improve this answer
And when that does not work either, stop the service and start copying your .mdf and .ldf files to a new instance, while developing grudge and hatred to all DBAs in the world... :) –  Roi Gavish Jun 3 '13 at 7:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.