ID Number: Q78817
1.10 1.11
OS/2
buglist1.11
Summary:
Problem ID: PRSQL9112004
SYMPTOMS
Attempting to log in to SQL Server generates the following errors,
and the user is denied access to SQL Server:
Server user id <id number> is not a valid user in database
<database name>
(Msg 916, Level 14, State 1)
Cannot open default database <database name>
(Msg 4001, Level 11, State 1)
CAUSE
If a login ID is created and that login is given a default database
(for example, "sp_addlogin login_id, password, default_db"), but no
user name is created in the default database for that login ID (for
example, "sp_adduser login_id, user_name"), that login ID should
still allow a user to log in to SQL Server. When the user tries to
log in, because there is no user name in that user's default
database, the above errors should be generated, and SQL Server
should log the user in to the master database.
SQL Server incorrectly handles the login process when a login ID is
assigned a default database but no user name is defined in the
default database for that login ID. SQL Server generates the above
errors, but then does not allow the user to log in at all, rather
than defaulting that user to the master database.
This problem does not occur, however, if the login ID's default
database contains a "guest" account, or if the login ID's default
database is MASTER.
WORKAROUND
To avoid this problem, when specifying a default database for a
login ID, be sure to also create a user name in the default database
for that login ID, or have a guest account available in the default
database.
STATUS
Microsoft has confirmed this to be a problem in SQL Server version
1.11. This problem does not occur in version SQL Server 4.2.
Additional reference words: 916 4001 sp_addlogin