Cannot open SQL 2000 default database
Carl Gross
I don't know how I did this, but when ever I try to open my SQL Server
Enterprise Manager I get the following error:
A connection could not be established to (LOCAL).
Reason: Cannot open user default database. Login failed.
Please verify SQL Server is running and check your SQL Server registration
properties (by right clicking on the LOCAL node) and try again).
I get this whenever I right-click on LOCAL.
Erland Sommarskog
Apparently you at some point set your default database, the one you connect
to when you login, but that database does not exist anymore.
Open a command-line window, run
osql -S . -E -d tempdb
And then run:
EXEC sp_defaultdb 'yourlogin', 'tempdb'
Notes:
1) I assume here that you run OSQL on the SQL Server machine itself,
and you have a default instance. You may instead have to specifyu
your server as MACHINE\INSTANCE - it's the same as you use in
Enterprise Manager.
2) I assume here that you use Windows Authentictaion (-E). Use -U and
-P to specify username and password if you use SQL authentication.
3) "yourlogin" is of course a placeholder for your real login name.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Rick Byham, MSFT
database is not recovering properly.
Try connecting to the server using Query Analyzer (isqlw.exe) and specifying
the database as part of the connection string. For instance, to connect to
your server using Windows Authentication and specify the master database,
execute the following command:
isqlw.exe -S <servername> -E -d master
Hopefully, that will get you past the default database error. Once you have
connected to the server, change your default database to master.
sp_defaultdb [ @loginame = ] 'whatever' ,
[ @defdb = ] 'master'
Now connect with Enterprise Manager if you want.
--
Rick Byham, MSFT
(Implies no warranty or rights)
"Carl Gross" <Carl...@discussions.microsoft.com> wrote in message
news:E8A868B5-0092-4B2B...@microsoft.com...
Carl Gross
running differently (slow) since I did the server (although it could be my
antivirus server, too).
"Erland Sommarskog" wrote:
> .
>
Carl Gross
on-line. Anything I should know about?
"Erland Sommarskog" wrote:
> .
>
Erland Sommarskog
> Didn't work. Is there something else I could try? My network seems to be
> running differently (slow) since I did the server (although it could be my
> antivirus server, too).
Exactly what does "didn't work" mean? On which point in this procedure
did it fail?
The problem is that your default database does not exist, so this needs
to be addressed.
I include the procedure again:
Carl Gross
1>
and I type in the EXEC command like this:
1> EXEC sp_default 'mylogin(I use my admin user name)', 'tempdb'
after that I get:
2>
I have to type EXIT or type ctrl-C to get out of the SQL editor. By
"nothing happens" I mean that I try to look at the databases and get the same
error as I did before the error, as though I didn't type the command prompt
code you gave me. Am I missing something that might execute the osql line I
typed in?
"Erland Sommarskog" wrote:
> .
>
Erland Sommarskog
> After typing in the "osql -S -E -d tempdb" command I get a line number:
>
> 1>
>
> and I type in the EXEC command like this:
>
> 1> EXEC sp_default 'mylogin(I use my admin user name)', 'tempdb'
>
> after that I get:
>
> 2>
>
> I have to type EXIT or type ctrl-C to get out of the SQL editor. By
> "nothing happens" I mean that I try to look at the databases and get the
> same error as I did before the error, as though I didn't type the
> command prompt code you gave me. Am I missing something that might
> execute the osql line I typed in?
Yes, you need to type "go" to tell OSQL to send the command batch to SQL
Server. Sorry, that I failed to mentioned that.
Carl Gross
I typed in every admin username and admin password in my arsenal with no
effect (after the EXEC command I typed "go" and got results).
I tried the following command:
EXEC sp_defaultdb 'sa', 'tempdb'
and got the following response:
Default database changed. Still can't get into LOCAL SQL Server Group. Did
I type in the wrong parameter?
"Erland Sommarskog" wrote:
> .
>
Erland Sommarskog
> I typed in every admin username and admin password in my arsenal with no
> effect (after the EXEC command I typed "go" and got results).
>
> I tried the following command:
>
> EXEC sp_defaultdb 'sa', 'tempdb'
>
> and got the following response:
>
> Default database changed. Still can't get into LOCAL SQL Server Group.
> Did I type in the wrong parameter?
regisration properties say?
Carl Gross
can't remember if there is a SQL Server username or password.
Is there a default that MS uses when its first installed?
"Erland Sommarskog" wrote:
> .
>
Erland Sommarskog
> I use the windows authentication. Since I have never had to program in
> it I can't remember if there is a SQL Server username or password.
In that case you should do
EXEC sp_defaultdb 'YOURDOMAIN\YOURLOGIN', 'tempdb'
> Is there a default that MS uses when its first installed?
How good would that be for performance?
But there is the Dedicated Admin Connection you can use when there is
no other way to connect.
Also, by default SQL authentication is disabled, so if you never enabled
it, it's off.
Carl Gross
Msg 15007, Level 16, State 1, Server SCAPCADC, Procedure sp_defaultdb, Line 44
The login 'DOMAIN\ADMINUSER' does not exist.
Is there something else I could try?
"Erland Sommarskog" wrote:
> .
>
Erland Sommarskog
> I tried it and got this error:
>
> Msg 15007, Level 16, State 1, Server SCAPCADC, Procedure sp_defaultdb,
> Line 44 The login 'DOMAIN\ADMINUSER' does not exist.
>
> Is there something else I could try?
When you are logged in, run this query:
SELECT system_user
And as login specify what it returns.