How to Recover SA password when you forget it – SQL 2005

What to do if you forgot SA password in SQL Server 2005?

In general if you forgot SA password or if the SA account is disabled these are the options to login into SQL Server 2005 and reset or enable SA.

1. If BuiltinAdministrator is present in SQL Server, you can login with an ID which is member of Administrators group and reset the SA password.
2. Or else if you have some other ID which is having sysadmin privilege in SQL level(this also SQLservice account), you can login with that and reset SA.

But in case if you have the following scenario where,
1. You have disabled SA (or) forgotten SA password
2. You followed the best security practice and removed Builtinadministrator from SQL Server

So you cannot login with a sysadmin ID into SQL Server, and you start thinking about uninstalling SQL Server 2005. No need to perform any uninstall and reinstall in such scenarios in SQL Server 2005 as you have this option where the Members of Windows Administrative groups have sysadmin privilege in SQL Server if you start SQL Server 2005 in Single user mode.

Consider this example, where I have disabled SA and removed BuiltinAdministrator from SQL Server 2005. Refer the below screenshots which displays the error message I get when I login Administrator and SA,

Refer the below screenshot which shows the members of windows administrator group,

I perform the following steps ,
1. Login with the ID Test @OS Level
2. Stop SQL Server 2005 using the command, NET STOP MSSQLSERVER *
3. Start SQL Server 2005 in Single-User mode using the command, NET START MSSQLSERVER /m *
4. Login into SQL Server 2005 using the ID Test as shown in the below screenshot,

*Note: MSSQLSERVER is for default instance, if you are proceeding in a named instance then use MSSQL$Instancename instead of MSSQLSERVER.

5. Since SQL Server is started in Single-User mode it will allow only one connection and hence you will get the following error if you click the New Query

6. Disconnect and close the Object Explorer and then connect using New Query you will be able to connect as shown below, and then enable SA login using the command

ALTER Login SA enable

7. If you want to create a new SQL Level login with sysadmin privilege , you can perform as shown in the below screenshot

8. Now you need to Stop SQL Server and start it normally using the command, NET START MSSQLSERVER * and connect using SA or the new login you created and proceed as shown below,