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

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

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