SQL Server Error 15023

SQL Server Error 15023

SQL server Error 15023: a user or role already exists in the current database

After restoring the database backup on the remote server to the local machine, an error occurs: "error 15023: A user or role already exists in the current database"

Cause:

The difference between "login" and "user" in SQL server is that "login" is used for user authentication, while the Database "user" account is used for database access and permission verification. Logon is associated with the user through the security identifier (SID. When the database is restored to another server, the database contains a group of users and permissions, but the corresponding logon or logon users may not be the same. This situation is called "isolated user ". In this case, you cannot create a new login or grant the "user" permission of the corresponding database to the login with the same name, because SQL Server Reports "error 15023: A user or role already exists in the current database"

Solution:

To solve this problem, call the system stored procedure sp_change_users_login. The usage is as follows:

Use Northwind

Go

Sp_change_users_login 'Update _ one', 'test', 'test'

Among them, Northwind is a database with isolated users. update_one is a stored procedure parameter, indicating that only one user is processed. The first test is "user", and the last test is "login ", the preceding SQL statement re-connects the Server login "test" and the Northwind database user "test. In this way, the database can be used normally.