Lost access to SQL Server. Now what ?

In this blog we will explain what to do to regain the access to your SQL Server instance. Here is our scenario :
– You have by accident removed all logins in SQL Server instance
– Your sa account by security and best practice is disabled
– So no possibility to login with a sqlcmd or your SSMS (SQL Server Management Studio) tools

First thing first, take a deep breath and be calm.

Then here are the steps to regain your access to the SQL Server instance

1- Connect to your server where the SQL Server instance is installed (do a RDP connection)

2- Make sure your are administrator on this server. This is really important

3- Stop the SQL Server instance by using the SQL Server Configuration Manager.

4- Run powershell as administrator

5- Navigate to the folder where the SQL Server executable is located. The default location is "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn" for SQL Server 2019

6- Type the following command and press Enter: (This command starts the SQL Server instance in single-user mode)
sqlservr.exe -m

7- Run another Powershell as administrator

8- Type the following command and press Enter: (This command will let you to connect to the instance)
sqlcmd -S localhost -E

9- Now all you need to do is to create a login with the sysadmin role
CREATE LOGIN [LIFESAVER] WITH PASSWORD = 'Pa$$word123456'
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [LIFESAVER]
GO

Here we go, Now you can restart your instance and login with your login freshly created and then recreate your removed logins again.

enjoy 😀