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