Recover sa password or gain sysadmin access to a database

Please do not re-install SQL server if you forgot/don’t know the sa password or you do not have sysadmin access to the database but you want to have. Try one of the below to recover the access.
This process of recovering lost sa admin account password in Microsoft SQL server which is applicable for Express version, SQL 2005, SQL 2008, SQL 2008 R2, SQL 2012, SQL 2014, SQL 2016 without being an admin. It uses command line (cmd). Once you gain sysadmin access, you can reset sa password in MSSQL.

1. Scenario 1: You have the access to the windows server administrative password and your SQL server instance has Windows Authentication enabled.
2. Scenario 2: You do not have windows server administrative password.

Scenario 1:
For Scenario 1, you can easily reset sa password using SSMS by following below steps:
Steps:

1. Login to the host windows SQL server as an administrator.
2. Open SSMS
3. Connect the database engine using Windows Authentication
4. After connection, using SSMS navigate to Security Node and expand Logins
5. Right click on sa login user
6. Change the Password and confirm.

Scenario 2:
For scenario 2, it’s not easy. Follow the below steps and get your access back.
Steps:

1. Open command prompt with ‘Run as Administrator’
2. Stop the SQL server instance that you want to get access for.
3. Then start SQL server in single-user mode by adding the parameter –m at the command line.
4. Connect the SQL server instance
5. Add your login to the sysadmin group
6. Exit from the connection
7. Stop the SQL server instance again and then start again
8. Connect with your login and change the sa password.

Below is the commands that I have used to get the sa password of my SQLExpress instance

1. Type ‘Cmd ‘ in run and right click on the icon and ‘Run as Administrator’
2. Net Stop MSSQL$SQLExpress
3. net start MSSQL$SQLExpress /m SQLCMD
4. sqlcmd –S.\SQLExpress
5. [EXEC] master..sp_addsrvrolemember @loginame = N'<DomainName>\<DomainUser>', @rolename = N'sysadmin'
6. Quit
7. Net Stop MSSQL$SQLExpress
8. Open SSMS with windows authentication. Change your sa password.