Cannot open user default database. Login failed. sql.80

Say you are doing a migration to a server and want to ensure that you can recover quickly, with minimal downtime. A good method is to restore a backup and do a quick switcheroo and do 2 sp_renames and the old database replaces the new screwy one.

Mission accomplished, your’e the greatest DBA in the world. Rollback complete.

Oh wait, stop. You aren’t that genius. When your database users try to connect to the database, you suddenly start receiving an error like

Cannot open user default database. Login failed.

Here’s what happened smarty pants. When you renamed the database, and had both database online, everything worked fine. Except people were now connecting to a different default database. Now that you dropped the new screwy database our system catalog tables are out of wack.

Do a simple check on the dbo.syslogins table and you will see many of your users now have a dbname = NULL.

To fix this simply enable system catalog updates:

sp_configure ‘allow updates’,1
go
reconfigure with override
go

Find which databases are referenced, but do not exist.

Databases are listed in the sysdatabases table.

select * FROM sysxlogins WHERE srvid IS NULL
AND dbid NOT IN (select dbid from sysdatabases)

Then update the database ids and replace them with a database that actually exists now.

update sysxlogins set dbid = 12
where dbid = 7

Dont forget to reconfigure so that the system catalog cannot be updated.

sp_configure ‘allow updates’,0
go
reconfigure with override
go

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s