Recover sa password

If you have forgot your ‘sa’ account password and do not have any other account with admin privileges available. Also as a best practice you ave removed the access from builtin\Admin account also.  You can follow below steps to recover/reset ‘sa’ account password:-

1) Go to SQL Server configuration manager and stop the SQL Server service for which you want to reset ‘sa’ password.

2) Right click on SQL Server service –> Properties –> go to advanced tab.

3) Under the advanced panel go to Startup Parameters option and add ;-m at the end of the current parameter values, without any spaces. -m option is used to start SQL Server in single user mode.

4) Restart SQL Server. This will start SQL Server in single user mode.

5) Open SQL Server management studio and open new query window. Here connect to the instance with windows authentication. SQL Server will allow you to connect as admin.

Note:- When you start SQL Server in single user mode and are connected to box with admin rights on physical server, SQL Server allows your windows account to act as a admin to SQL Server instance also.

6) Now create a new temporary account with sysadmin privilige.

create user temp with password = 'Temp123';

go

exec sp_addsrvrolemember 'temp,'sysadmin';

go

or, if you already have a account to which you want to grant sysadmin access, you can do that also.

8) Stop SQL Server service and remove ;-m from startup parameters.

9) Connect to SQL Server with the account we have created at step 7.

10) Now change your ‘sa’ account password and then connect using your new ‘sa’ account password.