SQL Server 2014 - Disabled All Admin Users

Question

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.

Answer 1

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!