Can not open user default database; login failed

Troubleshooting Authentication Issues

Problem:

Can not open user default database; login failed

Scenario:

I have windows DBA group which has default database DB1 and has SYSADMIN rights on SQL Server, by some reasons the DB1 database is corrupted and user is unable to login.

Solution:

Step 1:

Connect SQL Server using SQLCMD/OSQL as below and give the default database name as MASTER

sqlcmd -E -d master

Step 2: Now issue ALTER LOGIN statement and change the default database

Use SP_DefaultDB procedure to change the default database

sp_defaultdb (SQL Server 2000)

Changes the default database for a login

Syntax

EXEC sp_defaultdb 'Victoria', 'pubs'

(SQL Server 2005)

ALTER LOGIN LOGINNAME WITH DEFAULT_DATABASE = MASTER