SQL Server Error: 15023

SQL Server Error 15023: User or role already exists in the current database

Error: "Error 15023: User or role already exists in the current database" when a database backup on the remote server is restored to the local machine, and a "login" is associated with a "user" (user mapping).

　　Reason:

The difference between "Log on" and "User" in SQL Server, "Log on" is used for user authentication, and the database "user" account is used for database access and permission validation. Logins are associated with the user through a security identifier (SID). When you restore a database to another server, the database contains a set of users and permissions, but there may not be a corresponding login or the user associated with the login may not be the same user. This situation is known as "orphaned users." You cannot resolve a logon issue by creating a new login or by granting the user permission for the corresponding database to a login with the same name because SQL Server reports "error 15023: User or role already exists in the current database"

　　Solve:

To solve this problem, you need to invoke the system stored procedure sp_change_users_login, as follows:

Use Northwind

Go

Sp_change_users_login ' Update_One ', ' Test ', ' test '

Where Northwind is a database of orphaned users, Update_One is the parameter of the stored procedure, indicating that only one user is processed, the first test is "user", and the last Test is "login", and the above SQL indicates that the server is logged in "Test" and Northwind The database user "test" is reconnected. This makes it possible to use the database normally.