How to 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. How you will connect to SQL Server instance?
You can follow below steps to recover/reset ‘sa’ account password:-
1) Login to SQL Server box with account having admin privileges on the box.
2) Go to SQL Server configuration manager and stop the SQL Server service for which you want to reset ‘sa’ password.
3) Right click on SQL Server service –> Properties –> go to advanced tab.
4) 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.
5) Restart SQL Server. This will start SQL Server in single user mode.
6) 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.
7) 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.
Please note this will work for SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. These steps will not work on SQL Server 2000 and are not tested on SQL Server 2012. If you have forgot ‘sa’ password for SQL Server 2012 instance, its worth trying to see if this works or not.