"Error 21002" creating logins in SQL 2000

[semperfi91]

After restoring db from backup device I can not create a user and give them access to that db. The login information in the db is retained but not in the security/logins section.

Then when I try to create the account in the security/logins section I receive the "error 21002 user xxx already exists." Looked online and at (Article 819261) but the workaround is the problem.

Any assistance would be appreciated. Thanks.

/scott

 

[nigelrivett]

You can drop the users from the database by using drop user commands.

Also see sp_change_users_login which may be able to fix everything for you.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JayKusch]

Try looking at your other DBs in the Users tab. 10-1 you will find that the user you are trying to add is listed in there. Remove it and try to add him back in the Security section

Thanks

J. Kusch

 

[semperfi91]

Hi. Thanks for the advice. This user is only accessing this one DB. He owns objects which makes dropping him more difficult than it should be.

The sp_change_users_login returns the same errors that the GUI did.

 

[semperfi91]

Thanks for your tip. After playing with the sp_change_users_login, got it to work. Here is the exact string.

EXEC sp_change_users_login 'Update_One', 'User1', 'User1'

Thanks again. Cheers.

/scott