修改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或者其他系统数据库上执行

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

-- fix all orphan users in database
-- where username=loginname
DECLARE @orphanuser varchar(50)
DECLARE Fix_orphan_user CURSOR FOR
SELECT dp.name  As Orphan_Users
FROM sys.database_principals dp
left join sys.server_principals sp
ON dp.sid=sp.sid 
WHERE sp.name IS NULL 
AND dp.type='S' AND 
dp.name NOT IN ('guest','INFORMATION_SCHEMA','sys')

OPEN Fix_orphan_user
FETCH NEXT FROM Fix_orphan_user
INTO @orphanuser WHILE @@FETCH_STATUS = 0
BEGIN

EXECUTE('ALTER USER ' + @orphanuser + ' WITH LOGIN = ' + @orphanuser)

FETCH NEXT FROM Fix_orphan_user
INTO @orphanuser
END
CLOSE Fix_orphan_user
DEALLOCATE Fix_orphan_user

 

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