How to login to SQL Server if all logins with sysadmin role are disabled

Introduction

SQL Server 2016 creates a built-in login called "sa" with sysadmin server role. Additionally, several other built-in logins such as NT Service\MSSQLSERVER and NT Service\SQLSERVERAGENT are also created with the sysadmin fixed server role during the instance creation process (SQL Server installation).

Because it is not recommended to perform database administration with the "sa" login, you may have already created an additional login (new login) with the sysadmin role and and named it as sqlserver.

This article explains how to login to SQL Server if you have accidentally disabled the "sa" login and any other new logins created with the sysadmin role (aka "locked yourself") scenario.

Assumptions

The instance id is the default instance id MSSQLSERVER.

Solution

1)  Stop SQL Server (MSSQLSERVER) database engine Windows service.

2) Create a new Windows operating system (OS) user, for example, sqlserver_new and make it part of the Administrators OS group.

3) Login to the database server using Remote Desktop Connection as this new OS user sqlserver_new and then launch SQL Server 2016  Configuration Manager => SQL Server Services => SQL Server (MSSQLSERVER) => right click => Properties

Then add the -m startup parameter, which helps us restart SQL Server in single user mode. Please see below screenshot.

4) Click on Apply and close the window. Now right click => Restart

This will restart the SQL Server (MSSQLSERVER) database engine in Single user mode.

5) Make sure SQL Server Agent service is stopped. If it is started, then it will consume the single user by itself. We don't want that.

6) Now launch SQL Server Management studio (SSMS) as administrator. ( Right click => Run as administrator) .  You can login with the new sqlserver_new OS user even though this account is not created as an instance login inside the database instance.

Then once inside SSMS => Security => Logins and then enable,grant access to those existing sysadmin role accounts which were accidentally disabled (or) simply create a new login with a sysadmin role.

7) Now launch SQL Server 2016  Configuration Manager  again, this time remove the -m startup parameter and restart the SQL Server (MSSQLSERVER) service. This will help us get back to the multi-user mode again.

8) You can remove the OS user sqlserver_new if you no longer require it. This account was created specifically to fix the problem, so you may not need it.

9) And finally, start the SQL Server Agent service again.