SQL Server Error 4064: Error message when you connect to an instance of SQL Server: "Cannot open user default database"

I was doing a SharePoint reinstall at a client site today and was clearing out some of the databases on the sql server when all the sudden with 3 db’s left I got this nice Error message 4064 greeting me, and then I couldn’t log into SQL anymore.  After a few minutes of playing around I went onto the google machine and found the answer.

Turns out I detached the DB that was the set as the default database for the current user I was logged in with.  After some searching, turns out this is a pretty quick fix.

C:\> sqlcmd -E -d master

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

2> GO

Obviously change “[BUILTIN\Administrators]” to whatever login your having a problem with.  In addition, since my sql server was clustered, I had to specify the server name as well in with the sqlcmd, so mine was “sqlcmd –S SQLSERVERNAME –E –d master”

*** sqlcmd is a command that comes with SQL Server, do a search in the sql server program files to find it.

After the above I was all set, able to login to the server again, and finish up my work.