I was asked to investigate a problem with SQL Server 2005, whereby Administrators logging on to the SQL Server couldn’t login to the SQL Server Management Studio. An attempt to login to the Management Studio (or connect by SQLCMD for that matter) would get an Error 4064: Cannot Open Default User Database.
I Bing Searched the Error 4064 and found lots of information on it, but nothing really pointed to a cause or resolution to the problem, however I knew that the problem was related to the master system database.
The vast majority of sites suggested things but none of these worked for me. Being that my skill with SQLCMD is somewhat slim, I decided to aim for the Management Studio.
I knew that there was a problem with the master system database, and being that the master database is the database used to generate user connections, I thought I would try to login with a different database.
As per the image above, expanding the available options in SQL Server 2005 gives you the Connect to Database option. I set this to model being that I knew the system database model would exist and I didn’t know the names of any other databases on the server.
Excellent, it got me in. So the first place I looked was to make sure that the the master database was present. I figured it would be pretty difficult for SQL to start without a master database attached but anything is possible I guess.
Sure enough the database was there, but upon trying to view it’s properties, I got the same Error 4064.
I then tried to take a look at the server properties and I got the same Error 4064 again.
So I was poking around MSDN when I found the sp_defaultdb command, and I ran the following:
use master go sp_defaultdb [DOMAINUSER], master go
I logged out of the Management Studio and back in, but this time without the Connect to Database statement selected. I was able to login correctly to he normal master database because I had set my own.
This works for one user, but you don’t want to be going around setting the master as the default database for every user in the company otherwise your going to end up with a very long and complicated SQL configuration on your hands.
With the master database now correctly loaded for my user, I was able to view the server and database properties. First up was the master database and everything here looked good, so I moved on to the server. Whilst browsing through the server permissions, I saw this:
Notice how the default database location fields are blank? This is all under the Configured Values mode, so I switched to Running Values and they where blank too.
I entered the correct values into these paths (the SQL Server defaults in this case) and it’s working perfectly – Users are able to login (permission granted of course) without needing to specify a default database.
And that closes the case of the failed SQL Server login.