Microsoft KB Archive/196076

= "Cannot open user default database . Using master instead" =

Article ID: 196076

Article Last Modified on 12/15/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q196076



SYMPTOMS
When attempting to connect to Microsoft SQL Server, you may see the following message:

Cannot open user default database . Using master instead.

When attempting to login using Query Analyzer, you may see one of the following errors :

Msg 4062 Severity 11 Cannot open user default database '%.*ls'. Using master database instead.

Msg 4064 Severity 11 Cannot open user default database. Login fails.

If the client is using an ODBC or OLEDB-based connection, it will not be allowed to log in to SQL Server as the message suggests. If the connection is DB-Library-based, the client will be able to successfully log in to SQL Server and be in the master database.



CAUSE
This error may occur for either of the following reasons:
 * The SQL Server login uses Windows NT authentication. The login was added to a database by using the sp_addrolemember stored procedure, but it was never granted database access by using sp_grantdbaccess. Further, this database was made the default database for the login, and the guest account has been removed from the database.

-or-
 * The login's default database has been dropped and the login has never made a successful ODBC or OLEDB connection to the target SQL Server.



WORKAROUND
To work around this problem, do either of the following:   If the login was added as a user to the database without having been granted access to the database, the system administrator (sa) can grant the login database access by using the following SQL statements:

use go     exec sp_grantdbaccess go

NOTE: Do not specify the name_in_db when running sp_grantdbaccess because the name the user is known by in the database is whatever name was specified in the sp_addrolemember call. Also, SQL Enterprise Manager will show the login as already having been granted database access, so you will need to run the sp_grantdbaccess procedure to resolve this problem.

-or-   If the login's default database has been dropped, the sa can change the default database for the login, either by using SQL Server Enterprise Manager or by using the sp_defaultdb stored procedure:

use master go     sp_defaultdb,  go





MORE INFORMATION
If the login unable connect is sa, connect to SQL Server using command-line ISQL. Because ISQL uses DB-Library, you will be able to successfully connect and will be in the master database.

To use ISQL, perform the following steps:  Open an MS-DOS command prompt and change to the <Sqlroot>\Binn directory (by default in SQL Server 7.0, this is the Mssql7\Binn directory).</li> Type the following command to connect to SQL Server:

isql -Usa -P -S

</li> At the 1> prompt, issue the appropriate command from the Workaround section of this article to resolve the problem.</li></ol>

Additional query words: prodsql SEM can't connect failed GUI OLE DB dblib db-lib open connectivity

Keywords: kbprb KB196076

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.