by Derek Du

Today, when moving databases via backup and restoring, I had an issue of re-creating User Mappings on the new databases. An error, “User Already Exists in the Current Database,” prevents me from adding any mapping to the existing databases. Below is a solution:

This script shows the existing users of the selected database:
EXEC sp_change_users_login ‘Report’

This script fixes the user if you already have the login created:
EXEC sp_change_users_login ‘Auto_Fix’, ‘%user_name%’

This allows you to create a new login for the existing user:
EXEC sp_change_users_login ‘Auto_Fix’, ‘%user_name%’, ‘%login%, ‘%password%’

Reference:

User Already Exists in the Current Database – SQL Server

This blog post originally appeared in Derek's blog, Stuff.