Prompt SQL server error 15023 solution memo when modifying user mapping

When the database is restored from the backup file of another instance and users are added and mapped to the table, save the prompt that SQL Server Error 15023 user already exists. My solution:
EXECUTE sp_change_users_login ‘Update_one’,’login1′,’login1′
ALTER USER login1 WITH LOGIN = login1
Note that this is done on the corresponding DataBase (Use [DataBase]) and cannot be done on the Master or any other system DataBase
It is said that there is batch processing, not pro test, as follows:

 1 -- fix all orphan users in database
 2 -- where username=loginname
 3 DECLARE @orphanuser varchar(50)
 4 DECLARE Fix_orphan_user CURSOR FOR
 5 SELECT dp.name  As Orphan_Users
 6 FROM sys.database_principals dp
 7 left join sys.server_principals sp
 8 ON dp.sid=sp.sid 
 9 WHERE sp.name IS NULL 
10 AND dp.type='S' AND 
11 dp.name NOT IN ('guest','INFORMATION_SCHEMA','sys')
12 
13 OPEN Fix_orphan_user
14 FETCH NEXT FROM Fix_orphan_user
15 INTO @orphanuser WHILE @@FETCH_STATUS = 0
16 BEGIN
17 
18 EXECUTE('ALTER USER ' + @orphanuser + ' WITH LOGIN = ' + @orphanuser)
19 
20 FETCH NEXT FROM Fix_orphan_user
21 INTO @orphanuser
22 END
23 CLOSE Fix_orphan_user
24 DEALLOCATE Fix_orphan_user

 
References from the solution: http://www.sqlservergeeks.com/sql-server-error-15023-user-already-exists-in-current-database/
 

Reproduced in: https://www.cnblogs.com/hu123rong00/p/7116496.html


Read More: