SQL Server 2008 user, group, or role already exists in the current database

Question

I am trying to migrate an old, problematic Plesk server to a new one but there are some problems with SQL Server 2008.

Plesk couldn't migrate one of the DBs. So, I backed up the DB and restored on the new server. Then I tried to restore 'User Mappings' for the existing user but I got "User, group, or role already exists in the current database" error. I googled for solutions and found a few but they didn't work.

I tried to run the following procedure but no luck.

sp_change_users_login 'Auto_Fix', 'UserName'

I also tried to delete it's scheme as a solution but couldn't do it because - according to SQL Server - it's referenced by an object.

I don't want to change that object's user/scheme because I am afraid of to broke something.

What should I do?

Answer 1

Note : Whenever you move your database from one server to another server there is a problem of orphan user usually occur. FileListOnly is the new term in sql server that has all the details of the created backup that who has access to it.

So there are sequence of task that you have to follow

1. Firstly you have to restore the FileListOnly from the created backup to the destination or new server.
2. Restore the backup.
3. Use the sp_change_users_login as per the need. For help regarding this procedure you can refer http://msdn.microsoft.com/en-us/library/ms174378.aspx.

I put a example here hope it will help:

> RESTORE FILELISTONLY FROM DISK = N'C:\YourDB.bak'
> 
> RESTORE DATABASE YourDb FROM DISK = N'C:\YourDB.bak' WITH MOVE
> N'YourDB' TO N'D:\YourDB.mdf', MOVE N'YourDB_log' TO N'D:\YourDB.ldf',
> REPLACE
> 
> exec YourDB.dbo.sp_change_users_login 'update_one', 'UserName','UserName'

Answer 2

USE database;
GO
EXEC sp_change_users_login 'Update_One', 'username1', 'username1';
GO