Recovering SQL server built in administrator account

Question:

Built-in administrators account was accidentally removed and you don't have the SA password. If you are a member of the Administrators group in your machine and built-in administrators has no access to the database you can not connect to the database using the SQL Server Management Studio unless you will login using valid SQL Server account or impersonating other account using Run As option when you right click the shortcut.

Answer:

To recover the account follow these steps

1. Open SQL Server Configuration Manager (Programs -> Microsoft SQL Server 200X -> Configuration Tools).
2. Select SQL Server Services node.
3. Select the instance of SQL Server you want to recover.
4. Right click the instance and select Properties from the context menu.
5. On Properties form, select Advanced Tab.
6. Look for Startup Parameters and add "-m;" at the beginning. Make sure that there is no space between the semi-colon and the next set of parameters.
   
7. Click Apply button.
8. Click Ok button.
9. Stop-Start the SQL Server instance.
10. After SQL Server instance starts Windows Administrator can now access SQL Server using SQL command (sqlcmd). Be sure that your account is a member of Administrators group of the machine or domain.
11. Go to Start -> Run and type "cmd" then click Run button to launch the command prompt.
12. On the Command Prompt type "sqlcmd" and press enter. It will then move the cursor next to "1>". If your server is not the default database server you need to type "sqlcmd -S MACHINENAME\INSTANCENAME" just replace the MACHINENAME with your computer name and INSTANCENAME with your SQL Server instance name.
13. Type "EXEC sp_addsrvrolememeber 'BUILTIN\Administrators', 'sysadmin';" then press enter. This will gives the built-in administrators system administor role to the database.
14. Type "GO".
15. Type "exit" to exit the SQL Command.
16. After successfully restoring the BUILTIN\Administrators privilege. Repeat step 1-9 but on step 6 remove the "-m;" we added previously.

This works on both SQL Server 2005 and SQL Server 2008.

Warning: Please consult your action with the System Administrator and test it in a controlled environment before implementing it to production.

"Login Failed for user 'MACHINENAME\Administrators'. Reason: Server is in single user mode. Only one administrator can connect at this time."

This error is raised when you login using sqlcmd in step 12.

When we added "-m;" in step 6 we force the SQL Server to run in Single-user Mode. In this mode only one user is allowed to access the database server at a given time. Follow these steps to fix the error.

1. Open SQL Server Configuration Manager.
2. Select the SQL Services node.
3. Stop all services (Reporting Service, Analysis Service, and SQL Server Agent) with the same instance with the SQL Server you want to recover. Keep SQL Server service alive.
4. Repeat step 12.

After successfully restoring the account Start all the services we stopped in Step 3.

Adding Windows User while in Single-User Mode

Make sure that the account you use to login to the machine is a member of Administrators group. This also happen if the user has no access to the database. To add Windows user to the database follow these steps.

1. After successfully logged-in in Step 12 of the Solution, type "CREATE LOGIN [DOMAIN\username] FROM WINDOWS;". This action will add the Windows User to database Security Logins.
2. Type "GO".
3. Type "EXEC sp_addsrvrolemember 'DOMAIN\username', 'sysadmin';". This action will give the user the sysadmin role.
4. Type "GO"
5. Type "exit" to exit SQL command.