User, group or role ‘RSA_user’ already exists in the current database

Scenario: I backed up the vCenter SSO database and copied & restored it from an SQL2005 to an SQL2008 server. This worked fine but when I tried to add the user mapping I got the following:

 

Create failed for User 'RSA_user'. (Microsoft.SqlServer.smo)

SQL_orphan1

This occurred because the user I was trying to add already existed in the database. The user was created in this process but unable to map to the DB (RSA in this case).

To remove this orphaned user from the DB, first of all ensure the user exists in the DB.

USE RSA
GO
EXEC sp_change_users_login 'Report'
GO

The results should display the orphaned user. The final step is to run auto fix on the login.

USE RSA
GO
exec sp_change_users_login 'auto_fix', 'RSA_User'
GO

I was then able to map to the DB correctly.