"Cannot open user default database <ID>. Using master instead" (196076)

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