PRB: Problem Creating New Login

  - Microsoft SQL Server version 4.2 for OS/2

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.

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 has 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 still exist from the syslogins table in the master database to the sysusers table(s) in the other database(s). Note that this is also 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 examples of duplicate logins:

Using SAF Menu Functions

1. If the default database for the new login is the same as that for the login just deleted, the user receives the message, "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.

WORKAROUND

Under most circumstances, neither SAF nor the stored procedures 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 114 of the "Microsoft SQL Server System Administrator's Guide." This problem becomes immediately noticeable only if the numerically last login is dropped.