Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Connect to a SQL 2005 Server When You Are Completely Locked Out

By Rudy Panigas, (first published: 2009/11/03)

Think of this scenario.

  • 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.

If you use this batch file, copy it to your editor like Notepad and save is as a batch file with the .bat extension. If you are stopping an instance you will have to modify the file.

@echo off
cls
echo.*****************************************
echo.**** Shutting Down SQL2005 Services ****
echo.*****************************************
echo.
echo. Shutting Down Integration Services...
net stop "SQL Server Integration Services"
echo.
echo. Shutting Down Full Text Search...
net stop "SQL Server FullText Search (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Agent...
net stop "SQL Server Agent (MSSQLSERVER)"
echo. 
echo. Shutting Down Analysis Services..
net stop "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Shutting Down Reporting Services...
net stop "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Browser...
net stop "SQL Server Browser"
echo.
echo. Shutting Down SQL Server...
net stop "SQL Server (MSSQLSERVER)"
echo.
echo. 
echo. To stop the SQL Server Brower use this command: net stop "SQL Server Browser"
echo.
echo.
echo.**** Shut Down Completed ****
echo.

The service window should look like this once completed. See Image 2. 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. Hopefully you will find this article useful, actually I hope you'll never have to use it.

Thanks,
Rudy Panigas

PS. Below is a batch file you can use to start up all SQL Server related servers. I use both to stop and start SQL Server services as needed. If you have stopping an instance you will have to modify the file.

@echo off
cls
echo.---------------------------------------
echo.---- Starting Up SQL2005 Services ----
echo.---------------------------------------
echo.
echo. Starting Up SQL Server...
net start "SQL Server (MSSQLSERVER)"
echo.
echo. Starting Up SQL Agent...
net start "SQL Server Agent (MSSQLSERVER)"
echo.
echo. Starting Up Integration Services...
net start "SQL Server Integration Services"
echo.
echo. Starting Up Full Text Search...
net start "SQL Server FullText Search (MSSQLSERVER)"
echo. 
echo. Starting Up Analysis Services..
net start "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Starting Up Reporting Services...
net start "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Starting Up SQL Browser...
echo. 
echo. To start the SQL Server Brower use this command: net start "SQL Server Browser"
echo.
echo.---- SQL 2005 Servers Start-Up Completed ----
echo.

Resources:

image1.bmp | image2.bmp
Total article views: 29928 | Views in the last 30 days: 7
 
Related Articles
FORUM

SQL Server Service not starting

SQL Server Service not starting

BLOG

SQL Server – Start/Stop SQL Server from command line

You can start/stop SQL Server services using Services Console or SQL Server Configuration Manager. Y...

FORUM

Can not start MSSQL service by normal Windows account

Use Windows account instead of Local System account to start service to make more secure

FORUM

How do you hide the SQLAgent90.exe command window?

The SQLAgent service opens a blank dos window/command window. How do you hide the window?

FORUM

MYSQL server service faild to start.

After reboot server mysql service not starting.

Tags
administration    
security    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones