修改User Mapping时提示SQL Server error 15023的解决方案备忘

数据库从另外一个实例的备份文件恢复，添加用户并映射到表时，保存提示SQL Server error 15023 用户已经存在。我的解决方法是：

EXECUTE sp_change_users_login 'Update_one','login1','login1'

ALTER USER login1 WITH LOGIN = login1

注意的是在对应的数据库上执行(Use [DataBase])，不能再master或者其他系统数据库上执行

据说还有批量处理，没有亲测，如下：

 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

 

解决方案引用自：http://www.sqlservergeeks.com/sql-server-error-15023-user-already-exists-in-current-database/

 

转载于:https://www.cnblogs.com/hu123rong00/p/7116496.html