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

• 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.

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

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:

1. 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).



2. Type the following command to connect to SQL Server:
   

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




3. 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