Summary of SQL Server 2005 database migration issues-"error 15023: User or role already exists in the current database"

In the process of database migration, how to set up a bad user and login can cause " error 15023: User or role already exists in the current database."

Now tidy up the idea:

1, database backup ***.bak file in the original database;

2, in the new database, establish the same login name as the original database;

3, restore the backup database file ***.bak file on the new database, select overwrite the original database file;

4 . When you restore a database to a different server, you need to deal with an "orphaned user" issue. The specific solutions are as follows:

Users and permissions, but may not have the appropriate logins or logins associated with the user may not be the same user. This is known as the existence of "orphaned

User ". The logon issue cannot be resolved either by creating a new login or by granting the user permission to the corresponding database for a login with the same name because the SQL

Server will report "error 15023: User or role already exists in the current database", in order to resolve this issue, the system stored procedure needs to be called

Sp_change_users_login, the following is the specific usage:

Use Northwind
Go
Sp_change_users_login ' Update_One ', ' Test ', ' test '

Where: Northwind is a database that has orphaned users

Update_One is a parameter to a stored procedure, which means that only one user is processed.

the previous test was "use User "

The latter test is "login"

This SQL indicates that the server login "test" is reconnected with the Northwind database user "test" up. This will allow the database to be used normally.