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.

3 comments:

Hassan Mahmud said...

Thank you for the article in fact I have exactly the same error but now I am unable to delete the file and its giving me this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for User 'java'. (Microsoft.SqlServer.Smo)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Hassan Mahmud said...

Actually I found the solution and it was quite simple here is the website thought id post it here for anyone who might get the same error

http://www.mssqltips.com/sqlservertip/2620/steps-to-drop-an-orphan-sql-server-user-when-it-owns-a-schema-or-role/

gary said...

greats that you found the solution. cheers!