Post reply

Default database login error 4064

  • June 18, 2004 at 7:18 am

    #83242

    This one's got me stumped.

    I'm reattaching our live db to the test server and the other domain admins can't reconnect to the server using EM or QA. They can however login with existing application connections (because the default db has been set).

    The error message I get (in QA) is:

    I also ran a trace to see what the server error is: error 18456, which is really odd because they're apart of the domain admins group which are sysadmin already.

    Max

  • June 18, 2004 at 8:44 am

    #511051

    they're apart of the domain admins group

    Check that the login BUILTIN/ADMINISTRATORS exists on the test SQL Server.

    Your error message isn't readable.

    -SQLBill

  • June 18, 2004 at 8:58 am

    #511059

    Hi Bill

    The error message:

    Unable to connect to server TestServer

    Server: Msg 4064, Level 16, State 1

    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.

    The BUILTIN\ADMINISTRATOR login does exist and has sysadmin permissions with database access to all db's.

    I have a feelign that because the default db for admins in the live environment is a production database and the default on the test server is Master that the client connection settings (although we are using trusted security for Domain\Administrators here) is causing a problem.

    But the question is how do I reset that (if this is the error)?

    Another interesting snippet: when I add the developers Windows logins they get through...

    Thanks Bill

    Max

    Max

  • June 18, 2004 at 9:24 am

    #511065

    Max,

    I am just going to take a stab in the dark here, but..

    Is the name of the production database the same as the database on the testserver? If they are different and the logins in the testserver are identical to the production server, then they are defaulted to the wrong name of the database.

    Another possibility is that the user group in question, may not have the access to the default database that their login is set to.

    Dave Novak

  • June 18, 2004 at 9:32 am

    #511066

    Are you looking to change the default database for the login ? if yes then,  just double-click the login in Enterprise Manager and at the bottom it says Defaults. You can change the database here to your desired database.

  • June 18, 2004 at 9:43 am

    #511073

    Thanks for your replies

    The mdf name is different but the database name is the same. I've tried changing the default database for the domain administrators group to Master (no luck) and back to the test server database (still no luck). I have also confirmed that they are apart of the sysadmins database role and that they have been given the correct database access.

    Maybe the error message from EM will help:

    A connection could not be established to TestServer.

    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 TestServer node) and try again.

    Max

  • June 18, 2004 at 12:50 pm

    #511105

    Max,

    Check sysxlogins and sysdatabases on both servers for dbid of your database (sysdatabases) and what is default for the login (sysxlogins).

    I was able to reproduce your error when connecting using Query Analyzer after I replaced the value for a default  dbid for a test user with a number 10 which does not exist on this test computer.

    Are you sure that during the database move you did not run a script to update sysxlogins that developers created for you?

    Yelena

    Regards,Yelena Varsha

  • June 19, 2004 at 3:36 pm

    #511149

    hmm, that's precisely what I had in mind...

    Thanks for your suggestion, I reckon I'll try it first thing monday morning.

    Have a great weekend

    Max

    Max

  • June 21, 2004 at 3:42 am

    #511189

    Thanks Yelena, the problem was indeed that the default dbid in sysxlogins was incorrect.

    What I don't understand is that the developer said he restored the test db from a backup and as far as I understand that shouldn't change sysxlogins, or?

    Max

    Max

  • June 21, 2004 at 9:08 am

    #511269

    Max,

    I have lots of experience supporting both R&D developers and users that don't have lots of computer skills. I find it that regular users usually describe the problem exactly  because they don't know what is important. R&D developers are usually skilled programmers and in their descriptions of their actions they may leave out things that are obvious for them or they don't want to share. I normally would go to the developers desk and let them  to reproduce their actions. The range of the problems they create (from my experience) is from removing admin rights on NTFS to Administrators and Local System  so SQL Server can not even start  to creating installation / uninstallation packages that permanently remove SQL Server entries from the registry. I am not sure how the database restore can corrupt syslogins but I can suggest 2 scenarios:

    1. The developer used a script to restore a database that was created by another developer and included modifications to syslogins like dropping and re-creating logins and users. I have similar scripts to restore users sent to me by one application vendor. I had to put additional Where conditions to limit logins/users that are recreated.

    2. Your database may contan triggers(?)

    So try to reproduce what was done, maybe it is some  bug after all?

    Yelena

    Regards,Yelena Varsha

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply