ID Number: Q73559
1.10
OS/2
Summary:
SYMPTOMS
When trying to create a new login and user name, the following
message is received:
User already has a login under a different name.
This happens using either SAF or the system procedures (sp_addlogin
followed by sp_adduser).
CAUSE
This problem occurs only if the numerically last login [that is,
the login in the syslogins table with max(suid)] has been deleted
from the syslogins table and the username(s) associated with it
have not been deleted. Any login/user name combination created
thereafter may receive the above message.
In the sp_addlogin procedure, a unique identifier (suid) for a new
login is generated by using max(suid+1) in the query. If the
numerically last login in the syslogins table is deleted, its suid
will be reused by whatever login is created next. In addition, if
the user names previously associated with this suid were not
deleted, the mappings will still exist from the syslogins table in
the master database to the sysusers table(s) in the other
database(s). Note that this will also be the case if the last (n)
logins are deleted without deleting the associated user names since
all the suid's will be reused.
The following are two example situations in which the above can
happen:
Using SAF Menu Functions
------------------------
1. If the default database for the new login is the same as that
for the login just deleted, then the user receives the message
mentioned above, that is, "User already has a login under a
different name." The new user name is disregarded and the old
user name is granted privileges again.
2. The default database specified for the new login is different
from that for the login just deleted. In this case, the user
receives no message. The new login is created in the master
database. The new user name is created in the default database.
In addition, the mapping with the old user name is maintained.
This is possible because a login can have several user names
associated with it provided they are in different databases.
RESOLUTION
Under most circumstances, neither SAF nor the stored procedures
will notify the user to drop a login that has associated user
names. Thus, whenever a login is to be deleted, ALL the user names
associated with it (in one or several databases) must be deleted.
This is mentioned on page 83 of the "Microsoft SQL Server System
Administrator's Guide." This problem becomes immediately noticeable
only if the numerically last login is dropped.