2

I made the most ridiculous thing. I accidentally disabled both remote logins and database accesses for the Administrator accounts (including the one with Windows Authentication (Administrator account) and sa account on the production server! Please tell me a way that there is a way to access the server with administrator privileges! :(

Is it possible from an installation or some kind of command line etc?

Again, Administrator account IS disabled for SQL Server (but I have remote connection to server, the account itself is working...) So I literally cannot access to the sql instance with admin privileges.

Paul White
  • 83,961
  • 28
  • 402
  • 634
Mein Hat
  • 165
  • 1
  • 1
  • 7

1 Answers1

4

Yes, you can get back in. :)

  1. Go into SQL Server Configuration Manager
  2. Select SQL Server Services
  3. On the right side of the pane, find your instance and right-click for Properties
  4. On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add. (That's a dash then lower case letter m.)
  5. Click OK and restart.
  6. MSDN suggests launching Management Studio as an administrator for 2014. The traditional way is to use SQLCMD -E. Either way.

(If it doesn't let you because that account is explicitly denied, close the window, run the command line as a different user, and try again.)

Once you're connected, you should be able to create a new login

CREATE LOGIN [YOURDOMAIN\SomeUser] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [YOURDOMAIN\SomeUser];

or

CREATE LOGIN TempLogin WITH PASSWORD = 'SomeReallyGoodPassword';
ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;

and/or enable the disabled accounts.

ALTER LOGIN yourLogin ENABLE  
ALTER LOGIN AnotherLogin ENABLE

Because this is production and this requires a restart, you'll have to do it after hours. Good luck!

Katherine Villyard
  • 1,063
  • 7
  • 14
  • Heyya.. I don't know what I am doing wrong but this is so not working! I can confirm that SQL is now in single user mode (sql server agents are disabled) but when I try either SQLCMD -E or connecting from the SSMS, it still tells me that "INTPRODV01\Administrator" account is "disabled". I am seriously stuck and in trouble lol. And I know this is production but I already messed some stuff up, need to resolve this fast :D –  Jan 22 '16 at 17:33
  • Shift-right-click command or SSMS and choose "run as," then try a user that shouldn't be disabled. It should let that user in. (You may have to temporarily elevate that user.) – Katherine Villyard Jan 22 '16 at 17:34
  • Okay I managed that. But now it says its in single mode and only one administrator can connect here. But I have no connections anywhere from there? Is there something I am missing? Plus the other user is actually an administrator, it is a separate account... –  Jan 22 '16 at 17:37
  • I am literally lost... I have stopped every sql service out there but it still says another administrator is connected. How is that even possible? Please I am desperate :( –  Jan 22 '16 at 18:08
  • Did you close your other attempt to connect? – Katherine Villyard Jan 22 '16 at 18:28
  • Yes, literally everything is closed and all services are down... I am so confused... –  Jan 22 '16 at 18:32