Error 4064: Cannot open user default database. Login Failed.
Jill Von Seggern
multiple databases and we want them to choose which test database they want
to query when using ISQL. Over the weekend, I detached one of their test
databases and a dbo use only copy of the database, copied the files from the
dbo use only copy, then reattached both databases. This morning, when the
testers or any other users which are not part of the System Administration
group tried to connect to the SQL Server using ISQL they were getting:
Unable to connect to server \\SD_DEV:
Server: Msg 4064, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open default
database. Login failed.
We finally determined that the SQL Server is trying to connection them to
the dbo use only database, rather than the master database. As a
workaround, we changed that database from dbo use only to read only. They
are now able to get to the appropriate database, but they should be
connecting directly to the master. Any ideas why they are not?
The syslogins table appears to be correct. The database id's on the
sysdatabases table seem to make sense. Master is dbid 1 like it should be.
We tried dropping the login and recreating it, but that did not change
anything. The login uses Windows NT Authentication.
I hope I've provided enough information. Any help would be appreciated.
Thanks,
Jill
Erland Sommarskog
> This morning, when the
> testers or any other users which are not part of the System Administration
> group tried to connect to the SQL Server using ISQL they were getting:
>
> Unable to connect to server \\SD_DEV:
>
> Server: Msg 4064, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open default
> database. Login failed.
Hm, command-line ISQL uses DB-library, and should not give an ODBC
message. But I get that message from Query Analyzer when I try to
log in as a plain user in a dbo-use-only database. Do they use QA
(which is called isqlw as an executable.)
> The syslogins table appears to be correct.
It says "master" in the dbname columns for these users? And in
sysxlogins there is 1 in dbid for these users?
The one alternative I can think of is that there is a data source
involved and this data source has the db in question as default. I
also played with the though that they were saying things like:
isqlw -U sommar -P "" -d abaperls
on the command line, but this gives a different error message.
I would execute
sp_defaultdb user, master
for these users and see what happens
--
Erland Sommarskog, Stockholm, som...@algonet.se
Jill Von Seggern
Yes, they use Query Analyzer to query the database. I tried using the
sp_defaultdb proc and it said the user's default database had changed,
however, when they tried it again they still were connected to the other
database.
One thing I should say is that we had switched this server with another last
week. We renamed both servers and rebooted them. Then changed the local
server name in SQL Server(sp_addserver, 'sd_dev', local). We hadn't had any
problems, but this weekend was the first time we rebooted that server since
the switch. After the reboot this weekend, I had to run that sp_addserver
statement again, because the server was no longer listed as srvid = 0 in the
sysservers table. Evidently, we've missed a registry setting during the
switch. In 7.0, we just ran the SQL Server setup and the registry settings
were all updated. I tried this with this machine (which is SQL Serv 2000),
but it just wanted to install another instance of 2000. Could this have an
effect on their default db?
Thanks,
Jill
"Erland Sommarskog" <som...@algonet.se> wrote in message
news:Xns908629E3...@127.0.0.1...
Erland Sommarskog
> Yes, they use Query Analyzer to query the database. I tried using the
> sp_defaultdb proc and it said the user's default database had changed,
> however, when they tried it again they still were connected to the other
> database.
Strange.
> One thing I should say is that we had switched this server with another last
> week. We renamed both servers and rebooted them. Then changed the local
> server name in SQL Server(sp_addserver, 'sd_dev', local). We hadn't had any
> problems, but this weekend was the first time we rebooted that server since
> the switch. After the reboot this weekend, I had to run that sp_addserver
> statement again, because the server was no longer listed as srvid = 0 in the
> sysservers table. Evidently, we've missed a registry setting during the
> switch. In 7.0, we just ran the SQL Server setup and the registry settings
> were all updated. I tried this with this machine (which is SQL Serv 2000),
> but it just wanted to install another instance of 2000. Could this have an
> effect on their default db?
That seems very unlikely to me. But possibly this raises the question
which server are they actually trying to log into? Then again, when
you changed the status of the database on the server you thought they
were trying to log in to, they were able to log in, so that probably
means that they logged into that server.
Have you performed a
SELECT * FROM master..sysxlogins WHERE dbid = <id of the funny db>
One workaround could of course be to drop these logins and recreate them.
You will probably need to fix sysusers in a couple of databases in such
case.
Jill Von Seggern
changed their default database to one of our other databases, then changed
it back to master. This apparently worked, but I wish I had been able to
pinpoint the source of the issue. There has to be some system setting wrong
somewhere, I was just never able to find it. I'll just have to wait and see
if it happens again.
Thanks for your help,
Jill
"Erland Sommarskog" <som...@algonet.se> wrote in message
news:Xns90884E23...@127.0.0.1...
