Tip: How to connect to SQL when completely locked out

• You have forgotten (or do not have) the "sa" password
• The builtin\administrators account has been removed for security reasons
• You cannot connect to the SQL Server instance

Now what do you do? Let us see what you can do.

First off you need to shut down all the SQL Server related services. This can be done manually or you can use the batch file below. Either way you need to ensure that all SQL Server services are stopped. If you do the manual process see Image 1, which is a screen shot of the services you need to stop.

Now you can open a command line window by clicking on START then RUN finally type: cmd, which will open a new window. From here you execute the .bat file you saved.

Once executed you will see that all the SQL Server services have stopped. If not, stop anything that might still be running. This step is important because if any other SQL Server services are still running you will not be able to connect when SQL Server is started in single user mode.

Now you need to start just the SQL Server in single user mode. In the command line window change to the directory that has SQL Server installed. In my configuration it's here on the C: drive:

cd\program files\microsoft sql server\mssql.1\mssql\binn

This location may be different if you are accessing an instance.

Type the following in the command window for the default instance

sqlservr.exe -m

For a named instance type

sqlservr.exe -m -s <instance name>

Once this is executing you will see screen scroll and then stop. This process is not hung just waiting as SQL Server is now running in single user mode.

Next open another command line window (cmd) and now you can use the sqlcmd tool included with SQL 2005 installation. In this new window type the following and hit enter.

sqlcmd -E

A new prompt will appear. The buildin\administrators account needs to be created and added to the proper role. The following is what you would type in the new command line window:

create login [builtin\administrators] from windows
go
exec sp_addsrvrolemember [builtin\administrators], [sysadmin]
go
shutdown
go

The first line will create a login called builtin\administrators as per the one created within the OS. GO command executes the previous command. Now you add this login to the sysadmin role and finally you shut down the SQL Server that is running in single user mode. Note the first command line window exits the execution of SQL Server and returns the prompt. You may now close this window.

Finally start up all your SQL Server services again and login.

Info from Rudy Panigas