December 16, 2010 at 10:30 pm
Hi all,
In my env. we have disabled built-in\administrator, SA accounts
now we are not able to connect to sql server instance.
instance is default.
Plz suggest??????????
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
December 19, 2010 at 8:37 pm
anyone has idea???
plz suggest
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
December 19, 2010 at 10:55 pm
Are u able to login in Windows authentication mode?
December 19, 2010 at 11:30 pm
make sure you have "SA" or any administrator login before removing local\administrator login from SQL server Instance.
Ram
MSSQL DBA
December 19, 2010 at 11:34 pm
thats the problem
both the accounts have been disabled
and i don't have any other SA account available there.
do i have any other way ?
(other than reinstalling server)
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
December 20, 2010 at 2:49 am
No you don't need to reinstall the server.
See here:
December 20, 2010 at 6:28 am
Hi Please find the procedure to recreate the built in Administrators Account
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.
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.
---Code---
@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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply