4

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?

migrated from stackoverflow.com Aug 5 '12 at 15:19

This question came from our site for professional and enthusiast programmers.

3

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'
  • 1
    What if DB and user(s) is already created by Plesk? Should I use the same method ? – Tim Aug 7 '12 at 16:16
-1
USE database;
GO
EXEC sp_change_users_login 'Update_One', 'username1', 'username1';
GO
  • 6
    Please consider expanding this answer beyond just raw code. For example, you could explain what this does, and why you think it will fix the problem described in the question. – Paul White Feb 7 '15 at 13:35
  • @Milton, I second Paul White's comment. – Vérace Feb 7 '15 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.