Forgoten sa admin in sql server restore

The Problem:

A client asked me to make a few changes on SQL Server 2016 but after a few minutes we realized that sa admin was disabed and client wasn’t able to find the initial password or re-enable it.

How did we Manage to solve this issue

1.First of all we created a local admin with the name Jim (for example:)

To create a local admin just Open Server Manager from Start , and go to Computer Management

Next go to local users and groups, create a new local user by right click -> New user and fill the parameters as shown bellow.

Do not forget to go to Local Users – Groups , Administrators and ADD the new user to become an admin. Select the local server as shown bellow to add the user from there:

Next click ok and proceed to the step 2

2. Start SQL Server in Single mode.

There are 2 ways to start SQL Server in single mode. The first one (adding a -m to startup) did not work for me so i tried from cmd:

Run CMD as administrator and type

net stop MSSQLSERVER

after successfully stoping the service, write:

net start MSSQLSERVER /m

This will start the sql server service in single user mode where we can access and make changes

sqlcmd

If we are correct then you will see the 1> character and we are ready to write down the following sql commands: After each line hit Enter. Do not forget to change Localpcname bellow with your servername and Jim with the local admin which we created.

CREATE LOGIN [Localpcname\Jim] FROM WINDOWS
GO
ALTER SERVER ROLE sysadmin ADD MEMBER [Localpcname\Jim]
GO

after succesfully running the commands above, restart the service:

exit
net stop MSSQLSERVER
net start MSSQLSERVER

if there is no error we have succesfully created an sql admin so we can continue to the final step:

3. Login with the local admin Account and open SQL Manager

Open Start – Microsoft SQL Server 2016 – Microsoft SQL Server Management studio

Now you will be able to Go to Security – Logins – sa , and by double clicking on the username , go to properties and change the password !