Sql Server 孤立用户解决办法

美奇科技

　　Sql Server 孤立用户 是我们经常遇到的事情，今天详细的梳理了下，希望能帮到你
　　
当把用户数据库从一台 Sql Server 使用备份和恢复的方式迁移到另一台服务器。数据库恢复以后，原先用户定义的一些其他数据库用户，就无法在新服务器上继续使用了。尤其对一些Sql Ser 帐号，这些就是孤立用户。
Sql Server 的用户安全管理分两层，整个 Sql Server 服务器一层，每个数据库一层。一个用户，在每一层上都有帐号，在两个层面上都会分配不同的权利。在服务器层的帐号，交登录帐号(Login)，可以设置它管理整个 Sql Server 服务器，开启跟踪，修改 Sql Server 安全配置，备份所有数据库等。在数据库一层，叫数据库用户(DataBase User)，可以设置它对这个特定的数据库有读写、修改表结构、存储过程定义等权限。
服务器层面的安全，是设置在服务器的登陆账号上的。所有登录帐号的信息，可以查询 master 数据库里面的 sys.server_principals 这张视图。
数据库层面有“数据库用户”这个概念。每个数据库内部对象的安全性，例如表格的读写，是否讷讷感运行或修改存储过程等，都赋予在数据库用户上，保存在这个数据库内部。可以通过查询 sys.database_principals 了解用户信息。
Sql Server 登录帐号必须要和某个数据库用户相对应后，才能被数据库接纳。这个对应，就是要使得用户数据库  sys.database_principals 里面 SID 和 master 数据库 sys.server_principals 里的 SID 匹配起来。一个登录账户和数据库用户的名字可以不一样，但是 SID 必须一样。
当用户数据库恢复到新的服务器上后，master 数据库 sys.server_principals 里并没有这个帐号。但是用户数据库里还有 这个数据库用户。于是这个用户被“孤立”了。
　　
解决办法：
1. 备份时，要把系统数据库一并备份了，再恢复的时间，也把数据库恢复了
2. 通过 sp_change_users_login 来更改用户
如要检测孤立用户：




User;
Go;
sp_change_users_login @Action='Report';
Go;
　　
　　
恢复孤立用户：




User;
Go;
sp_change_users_login @Action='update_one',@UserNamePattern='',@LoginName='';
Go;
　　
　　
需要说明的是，sp_change_users_login 只能重新链接 Sql 登录帐号。对于数据库用户所对应的是 Windows 登录帐号，如果SID 不同，说明域也发生了变化，是不能通过这种方式连接在一起的。
　　
　　更先进的方法
　　
　　经过多次排除孤立用户发现，一般孤立用户都是好几个同时出现的，而手工一个个来排除效率太低，又容易出错，所以写了一个比较智能的存储过程，一次排除当前库中的所有孤立用户：
　　



declare @username nvarchar(50)
create table #temp_user(
  username nvarchar(50),
  UserSID int
)
insert into #temp_user exec sp_change_users_login @Action='Report'
declare temp_cursor cursor for
select username from #temp_user
open temp_cursor
fetch next from temp_cursor into @username
WHILE (@@FETCH_STATUS=0)
begin
exec  sp_change_users_login 'Auto_Fix', @username, NULL, @username;
exec  sp_change_users_login @Action='update_one',@UserNamePattern=@username,@LoginName=@username;
fetch next from temp_cursor into @username
end
close temp_cursor
deallocate temp_cursor
drop table  #temp_user
　　
　　
通过 "孤立用户" 的处理可以看出来：
1. Sql Server 的安全分两层，分别为：Server 和 DataBase
2. 备份和恢复数据库是很重要的
　　
　　
本文出处：Sql Server 孤立用户解决办法