User, group, or role ' ZGB ' already exists in the current database. (Microsoft SQL Server, error: 15023)

When using SQL Server, we often encounter a situation where a database on one server needs to be transferred to another server. When the transfer is complete, an error often occurs when you need to associate a "user" with a "login":

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

This problem is very difficult, after a number of reasons and solutions are found, because this problem and solution are more complex, so the process of some of the experience recorded to share with you, hope to be able to help you in the future similar operation.

The reasons and solutions are as follows:

First introduce the difference between "login" and "User" in SQL Server, "login" is used for user authentication, and the database "user" account is used for database access and permission validation. The login is associated with the user through a security identifier (SID). When you restore a database to a different 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". 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 SQL Server reported "error 15023: User or role already exists in the current database", and in order to resolve this issue, the system stored procedure needs to be called Sp_change_ Users_login, the following is the specific usage:

' Update_One ' ' yjsy312 ' ' yjsy312 '

where Netzs for the existence of orphaned users of the database, Update_One is the parameters of the stored procedure, indicating that only one user is processed, the previous yjsy312 is "user", the latter yjsy312 is "login", the above SQL represents the server login "yjsy312" and Netzs The database user "yjsy312" is reconnected. This will allow the database to be used normally.

User, group, or role ' ZGB ' already exists in the current database. (Microsoft SQL Server, error: 15023)