SQL Server Error User Group or Role Already Exists in the Current Database

Last week we migrated our SQL Server to a new server and new SQL Server version (2012). During migration we created the login users in the new server and then restored the databases. However the user mappings did not work. When we tried to change the mappings SQL Server returned the following error:

Create failed for User (Microsoft.SqlServer.Smo)
SQL Server Error User, group, or role already exists in the current database. (Microsoft SQL Server, Error: 15023)

To resolve this issue I followed the following approach:

First add the existing users without altering any user mappings in the new server.

Then, execute the following stored procedure to bind the user mappings from the old server to the new one:

use [database_name]
exec sp_change_users_login 'AUTO_FIX', '[login_name]'
go

After that you can check the login properties and you will see that old user mappings are restored in your new SQL Server.

If everything is OK you will see the following output:

The row for user  will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

Hope it helps!

Originally published at http://www.weboideas.com on December 23, 2014.