"Cannot open user default database"

[2005 Developer Edition] In Management Studio I changed my default database to a user db. I subsequently unselected that db as the default leaving me with no default db (I think - it's been awhile and I may not have this exactly right). But regardless, I can no longer Connect to Server using Management Studio (Windows authentication) - I get

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

The only thing suspicious in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG is

The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.

From Configuration Manager > SQL Server > Properties > Advanced > Startup Parameters

-dC:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf

Any idea what's going on here?

Hi,

I guess these are two different errors. The one first mentioned can be changed by logging on with a sysadmin role account or securityadmin role account and using the statement sp_defaultdb 'DANA\Administrator','master'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Management Studio cannot connect to the database engine, so what other tool or utility do I use to execute sp_defaultdb?|||

See if this thread helps you: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=388163&SiteID=1.

You should try specifying the database you want to use at connection time as shown in the above thread.

Thanks
Laurentiu

|||You can specify the database during the connection within Management Studio by clicking on the options button, remove the default value and select a valid database. Or use another tool like SQLCMD or OSQL to connect to the server with specifying a database.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||The thread reference did the trick - thanks a lot! BTW the original problem was caused by takinig the default (user) database offline, then exiting Management Studio.