Thursday, September 26, 2013

Create failed for User 'XXX' when restoring a database

Recently I was doing the database (let's say: 'restoreTest') migration in MSSQL 2008 from one instance to another.


There's an existing login (let's say: 'restore' in picture below) that I need to bring along with this 'restoreTest' database migration.


1) When I went to create a new login and assign the access-right of the newly-created 'restore' account to the new 'restoreTest' database restored in the new instance,


2) I encounter this error message:

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


3) The new 'restore' user is created but it's not associated with  'restoreTest' database.
I was so curious what went wrong. After a check, I  found the user 'restore' was backed-up along with the database.


4) After I deleted the user 'restore' that comes in the newly-restored 'restoreTest' database & , and re-assign it, then everything is OK.