02 June 2012

Cannot open user default database. Login failed.

I encountered this error when I tried to open a new connection using SQL Server Management Studio (SSMS): Cannot open user default database. Login failed.. It was a bit puzzling because my application using the same user account and password can connect to the required database.

It turns out that when SSMS starts a new connection, SQL Server expects the user account to be assigned to a default database (per the message) while my application could always connect since a database is specified in the connection string.

When a client connects to a SQL Server instance without specifying a database context, the default database defined for its login is used. If that database is unavailable for any reason, the above message appears.

The fix is to specify the default database for the user in their Login Properties.

I think when the DBA restored my database, the previous database was first deleted, so the default database setting was also deleted for my user account.

Reference

Cannot open user default database. Login failed.