locked
Cannot Open User Default Database (error 4064)

    Question

  • Hi,

    SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

    Cannot Open User Default Database, Login Failed
    Microsoft SQL Server, Error 4064

    Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
    (ıf you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )


    Tuesday, December 13, 2005 12:51 PM

Answers

  • You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

    sqlcmd -d master -U sa -P mypassword

    and then:

    alter login sa with default_database = master

    Thanks
    Laurentiu

    Saturday, January 21, 2006 1:29 AM
    Moderator
  • I see now that you mentioned that you don't know sa's password.

    sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

    sqlcmd -E -d master

    then issue the ALTER LOGIN statement.

    It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

    Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

    Thanks
    Laurentiu

    Thursday, February 09, 2006 6:41 PM
    Moderator
  • I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

    C:\> sqlcmd -E -d master

    1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

    2> GO

    I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

    Monday, May 08, 2006 12:09 AM
  • You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

    Thanks
    Laurentiu
    Tuesday, December 13, 2005 5:03 PM
    Moderator
  • I had the same problem, I was using SQL Server 2005 and deleted the database to which my default Windows Authenticated login maps. (I had my sa password but it would not let me log in using it saying that account was not assocaited with a trusted connection.)

    Anyway, when I tried to connect to the server via management studio it also told me it could not connect because it can not open the default database. So at first I also thought I could not fix this since I could not log in.

    After reading this post, here is how I got around it:

    Run SQL Server Management Studio, and on the initial "Connect To Server" dialog box, click "Cancel". Now under your regsitered server list, find the local server, right click on it and select properties. Go to the connection properties tab and enter "master" for "Connect to Database:". Now save it, and voila, you can connect again!

     

    Monday, June 05, 2006 3:39 PM

All replies

  • You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

    Thanks
    Laurentiu
    Tuesday, December 13, 2005 5:03 PM
    Moderator
  • I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.
    Friday, January 20, 2006 10:41 PM
  • You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

    sqlcmd -d master -U sa -P mypassword

    and then:

    alter login sa with default_database = master

    Thanks
    Laurentiu

    Saturday, January 21, 2006 1:29 AM
    Moderator
  • I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?
    Thursday, February 09, 2006 5:48 AM
  • I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

    I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

    Thanks
    Laurentiu

    Thursday, February 09, 2006 6:26 PM
    Moderator
  • I see now that you mentioned that you don't know sa's password.

    sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

    sqlcmd -E -d master

    then issue the ALTER LOGIN statement.

    It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

    Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

    Thanks
    Laurentiu

    Thursday, February 09, 2006 6:41 PM
    Moderator
  • To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?
    Friday, February 17, 2006 2:31 PM
  • You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

    Thanks
    Laurentiu

    Friday, February 17, 2006 9:54 PM
    Moderator
  • I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

    Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

    I then tried deleting my login. Failed again with error 4064.

    Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

    Hope this helps someone out there...

    Monday, April 24, 2006 9:10 PM
  • I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

    C:\> sqlcmd -E -d master

    1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

    2> GO

    I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

    Monday, May 08, 2006 12:09 AM
  • I detached a database on a network server with the idea of reattaching an updated version from my local db.

    Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

    When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

    I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

    How can I accomplish this? Can someone please help???  

    Wednesday, May 10, 2006 10:36 PM
  • The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

    How can I resolve this!!!!

    Microsoft, please help..........

     

    Wednesday, May 10, 2006 10:49 PM
  • For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....
    Wednesday, May 10, 2006 10:53 PM
  • I tried what you're mentioning Chris and it didn't work.

    In the query window I have the following:

    IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

    DROP LOGIN [sa]

     

    The results state the following:

    Msg 15405, Level 16, State 1, Line 3

    Cannot use the special principal 'sa'.

    Wednesday, May 10, 2006 10:55 PM
  • Have you been able to successfully set the default database of sa back to master, using the ALTER LOGIN command as explained earlier in this thread? If not, what error did you get. If yes, and you still get an error, please check that the default database was set correctly for sa by executing:

    select default_database_name from sys.sql_logins where name = 'sa'

    The result should show master. If you're getting any other errors, please copy the error number, state, and message and post them here.

    Thanks
    Laurentiu

    Wednesday, May 10, 2006 10:57 PM
    Moderator
  • I was able to fix it by doing the following:

    After I disconnected from the db (yeah, that's right, DISCONNECTED), I issued the following command in the network server sql window:

    use master

    go

    sp_defaultdb sa, master

    go

     

    After that, I was able to log in and perform actions against the db objects.

    Wednesday, May 10, 2006 11:06 PM
  • I was having a database connection problem with SQL Express too.  I did a lot of searching and found lots of different people with different answer... some worked for some and some didn't.  In my case I'm using the following approach in my web.config file for attaching to the DB and the solution is in bold below:

    <add name="SqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=pathtodb.mdf" />

    It didn't make a lot of sense to me to have to go mess with all of the Sql Mgmt Studio to have to grant a bunch of roles as I hadn't done this when I set up the project... it was all configured correctly by VisualStudio when I created it.  Closing the connection (was already closed) and not running Visual Studio (not a viable option anyway) was not fixing it.  I created another SQL database with Visual Studio to try to compare settings.  The roles/permissions checked out to be the same.  Finally I checked the permissions on the file itself (must not have the simple security model from XP turned on to see the Security tab).  The new SQL database file had an additional user with permission granted to it... the one that was failing did not.  The user was some long SQL generated user.  All I did was grant ASPNET permissions to the DB files and it started working again.

    Two reasons why this migh have gotten messed up.

    1) I moved the sql database files to another directory.  I don't think this was it as it worked for a few days and I really think the permissions should have come over with it.

    2) I just installed the SQL Mgmt Studio and altered a table schema with that.  Could SQL Mgmt Studio have altered the permissions on the DB files?

    Anyway just thought I'd share what worked for me. The order I'd diagnose this in the future is.

    1) make sure the database isn't open in VisualStudio or Sql Mgmt Studio (you don't need to close them down... just disconnect the DB.

    2) Check the permissions on the DB files (granted to some SQL account or ASPNET)

    3) Last resort is mess with DB roles.  I don't think this should be required if your just doing the basics from Visual Studio though.,

    Friday, May 12, 2006 4:50 PM
  • I have tried the process discussed in this thread and it did not work for me.  Doing a "Select dbname from sys.syslogins where name = <blah>" shows that the dropped database is still the default database name for the user. 

    What I could get to work was to simply click the Options button on the connection window in Management Studio and then to typein master as the database to connect to <note you can't browse the server as it will try to connect to the default database - you must type the dataabase name in>.  Once connected and in the master database I could execute the Alter Login statement and that sorted it out.

    If there is a bug then it is not in Management Studio but in SQL Server - Shouldn't SQL Server prevent detaching a database where logins have it set as their default database?  After all at present the syslogins are in an invalid state if you do detach such a datbase.
    Monday, May 22, 2006 1:10 PM
  • I had the same problem, I was using SQL Server 2005 and deleted the database to which my default Windows Authenticated login maps. (I had my sa password but it would not let me log in using it saying that account was not assocaited with a trusted connection.)

    Anyway, when I tried to connect to the server via management studio it also told me it could not connect because it can not open the default database. So at first I also thought I could not fix this since I could not log in.

    After reading this post, here is how I got around it:

    Run SQL Server Management Studio, and on the initial "Connect To Server" dialog box, click "Cancel". Now under your regsitered server list, find the local server, right click on it and select properties. Go to the connection properties tab and enter "master" for "Connect to Database:". Now save it, and voila, you can connect again!

     

    Monday, June 05, 2006 3:39 PM
  • I had an SQL Express DB in my ASP.NET app_Data folder and got the same error. The problem was that I had the databased open in server explorer inside Visual studio. When I close the connection, it starts working.
    Wednesday, June 07, 2006 11:54 AM
  •  Jonathan Roy wrote:

    I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

    Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

    I then tried deleting my login. Failed again with error 4064.

    Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

    Hope this helps someone out there...

    I was having the same issues in SQL2005 when logging into the default database. Somehow when I detached the database I was working with...the login no longer worked. Is this gonna happen everytime a database is detached? However the option above submitted by Jonathan Ray seemed to work for me. I am now able to log into the default database. Thanks for all the great ideas on correcting these issues.

     

    Wednesday, June 07, 2006 2:48 PM
  • MasterG. You RULE!!!

    Thanks so much for the RIGHT answer.

    I renamed my first SQL 2005 database, and thereafter could no longer connect using SQL Server Express Management Studio, also getting

    Cannot open user default database. Login failed.
    Login failed for user 'myMachine\myUsername. (Microsoft SQL Server, Error: 4064).

    Geeze, an obvious oversight. I'm sure glad I didn't have to go find out how to use the command line tool the MSFT guy was trusting on folks. I still do need to figure out how to changethe default database though. Maybe I have to go back to the command line yet.

    (PS Microsoft, this took me 4 hours to work-around/figure out...)

    Friday, June 16, 2006 4:57 AM
  • Whoops, I spoke too soon. I can view objects, but no longer backup my DB, or presumably do anything else.

    I think Bill Yeager has the solution for me:

    After I disconnected from the db (yeah, that's right, DISCONNECTED), I issued the following command in the network server sql window:

    use master

    go

    sp_defaultdb sa, master

    go

    Unfortunately I've never yet spent weeks with SQL Server and don't even know how to disconnect (my error when trying to do a backup says I can't connect, so presume I don't need to actually disconnect somehow, right?!). Also where does one find the "network server sql window" - in the management studio tool or somethjing else.

    Is this with the full SQL Server 2005 or the Express version?

    Thanks!

    - J

    Friday, June 16, 2006 5:07 AM
  • Found my answer just a bit further down. Check out Jonathan Roy's answer, as repeated by Bishop32. That did the trick, thanks!
    Friday, June 16, 2006 6:05 AM
  • Wow, this falls into the "wave a dead chicken over your head and turn around 5 times under a full moon" category.  Thanks Invisibl3, closing the connection did it for me.

    Rob

    Monday, June 19, 2006 9:01 PM
  • Just ran into this same problem.  if you are using Windows authentication, hopefully you have your own user account and rarely use the administrator account for logging on.

    For me, my server is in a domain so all I had to do is log off with my user account and login with the domain administrator account.  Open SQL MS, go to Security, logins and properties of my regular user account.  There change my default database to Master.

    Log off and back on with my user account and everything is fine.

     

    Friday, June 30, 2006 1:12 PM
  • Lothan, Thank you for this fix, I would have never suspected this reckless program logic in sql server. I took my default db offline & couldn't log in as me,sa,et al...

    MS: This problem wasted 2+ hours of my time today. It required server reboots, script & manual fix attempts, etc... Along with my toasted day came a lot of angry users who now really don't care for sql server 2005. How about changing the program logic. Instead of killing logon entry into sql server 2005, check permissions and map to master or diff db, then notify user of the occurrence... Or, at least notify the user of the real problem...

    Tuesday, August 22, 2006 3:00 PM
  • Hi Bill,

    If you can run queries against master Database try this one:

    sp_attach_db 'database_name, 'full_path_to_attached_database_data_file', 'full_path_to_attached_database_log_file'

    Example would for a testdatabase is

    sp_attach_db 'testdatabase', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testdatabase.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testdatabase.ldf'

    Hope this helps.

    Best,

    Michael Sh

    PS - Avoid detaching from a default database, took me over two hours to get it back!

    Tuesday, September 19, 2006 10:15 PM
  • I was unable to execute the command using the command line, I had to open a connection to the database using VB and then executing the ALTER LOGIN SQL.

    I copied the command given _exactly_ how it is and it kept telling me error near 'LOGIN'.
    Monday, October 02, 2006 3:34 PM
  • I am locking this thread as the issue has been clarified already and allowing additional posts only make this thread harder to read. If you're still hitting related issues, please open separate threads.
    Friday, October 06, 2006 6:48 PM
    Moderator
  • After spending hours trying to figure out how to solve this problem, I got the answer based on your comments. This is what I did:

    1. The first trick was to understand that I could use as default any database in my hard drive even if it does not show up in the SQL Management Studio. Basically what we are doing here is attaching the database and at the same time assigning the user.

    2. I looked for any existing database in my hard drive. Of course, I found master.mdb and at that point I understood why your examples always use that database.

    3. In the DOS prompt, I ran the following command:

    c:\> sqlcmd -S AIR\SQLEXPRESS -d master

    Where AIR is the name of my machine and SQLEXPRESS the name of the SQL Server instance.

    In the sqlcmd prompt I put the following:

    1> alter login [AIR\alvaro ramirez] with default_database = master

    Where AIR is my machine's name

    Another problem I was having was the missing []. Because the username I have in my computer has a space, the [] are required.

    2> go

    3> exit

    It worked after that. Hopefully this is useful to somebody else.

     

    And don't detach the new default database again or you will have to come back to this notes!!! In fact, what I learned is that I will leave the master database FOREVER so I do not have to worry about assigning a new default one.

    Thursday, November 30, 2006 11:00 AM
  • hay Alvaro Ivan

     

    Thanks this work for ......

     

     

    After spending hours trying to figure out how to solve this problem, I got the answer based on your comments. This is what I did:

    1. The first trick was to understand that I could use as default any database in my hard drive even if it does not show up in the SQL Management Studio. Basically what we are doing here is attaching the database and at the same time assigning the user.

    2. I looked for any existing database in my hard drive. Of course, I found master.mdb and at that point I understood why your examples always use that database.

    3. In the DOS prompt, I ran the following command:

    c:\> sqlcmd -S AIR\SQLEXPRESS -d master

    Where AIR is the name of my machine and SQLEXPRESS the name of the SQL Server instance.

    In the sqlcmd prompt I put the following:

    1> alter login [AIR\alvaro ramirez] with default_database = master

    Where AIR is my machine's name

    Another problem I was having was the missing []. Because the username I have in my computer has a space, the [] are required.

    2> go

    3> exit

    It worked after that. Hopefully this is useful to somebody else.

     

    And don't detach the new default database again or you will have to come back to this notes!!! In fact, what I learned is that I will leave the master database FOREVER so I do not have to worry about assigning a new default one.

     

     

     

    thkxxx once again

    Saturday, March 31, 2007 8:13 AM