I was migrating database from testing environment to customers production environment few days ago. Usually before I migrate that database, I did create a user for that instance, but on this one, I did migrated it first before create a user. And the problem happen when I want to create a new user and want to mapped this user to the database I’ve just migrated, I got error message create failed for user ‘username’. The complete error message was just like the following picture :
What happen? I’m sure that I’ve never create this username in this new instance before, but why the SQL server told me that this username was already exist. I finally realize that there is already a username on that database. I think when I’ve migrated this database from testing environment, each users that have permission to that database also being migrated to the new environment, even though that user still cannot be used for login to SQL Server.
I’ve found the answer here. It’s really simple to solve this issue, just open new query window and type the following T-SQL code :
USE <databasename> GO EXEC sp_change_users_login 'Auto Fix', 'username' GO
After I’ve executed that T-SQL code, everything back to normal, I’m able to mapped that user to my desired database, and also I’m able to login to that SQL Server instance using username I’ve just created. thank you.