修改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/