SQL 2008 default database detached. Can't login.
Posted on 2011-03-18
ISSUE: SQL Server 2008 database has been detached. All logins had this database as default database. Now, all users can log into the server and can run queries on the other databases on the same server, but none of the logins can perform any actions from Management Studio, i.e., backup, restore, view or edit database or login properties, etc.
The error displayed is:
"Cannot open user default database. Login Failed.
Login failed for user 'sa'. Microsoft SQL Server, Error: 4064"
The server is SQL Server 2008 running on a Windows XP development box.
The databases are all SQL Server 2000 compatible.
REQUEST: Is there a way to regain access at some level so we can re-attach the database? Possibly change the login's default database via TSQL query?