PRB: "Cannot Open Default Database <ID>. Using Master Instead"

ID: Q196076

The information in this article applies to:

Microsoft SQL Server version 7.0

SYMPTOMS

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

Cannot open user default database <ID>. Using master instead.

When attempting to login using Query Analyzer, the following error occurs:

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

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 <database name>
      go
      exec sp_grantdbaccess <login name>
      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 <login name>, <new default database name>
      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).

Type the following command to connect to SQL Server:

isql -Usa -P<sa password> -S<server name>

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

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


Keywords          : 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbprb

Last Reviewed: August 12, 1999