数据库实战：SQLServer孤立用户原因和处理技巧

一、SQLServer孤立用户产生的原因

服务器实例上未定义或错误定义了其相应 SQL Server 登录名的数据库用户无法登录到实例。

删除了对应的 SQL Server 登录名，则数据库用户可能会变为孤立用户。

将SQL Server 数据库移动到另一台非Sa登录名的服务器时会出现这种情况。这个是大家日常遇到最多的情况。比如我们需要排查问题的时候需要从生产环境备份数据库到本地进行问题排查，如果使用的是非sa账户的话，这个时候还原到测试服务器的SQLServer时候，再使用这个非sa账户登录访问的话就会提示不能正确访问数据库 错误代码是4064。

二、原理剖析

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 视图里并没有这个帐号记录。但是从其他服务器还原的数据库里还有 这个数据库用户。然后这个用户被“孤立”了。

三、解决方法

解决办法一、使用sp_change_users_login存储过程

检测数据库是否存在孤立用户

USE 数据库名称;
GO
 
EXEC sp_change_users_login @Action = 'Report';
GO

恢复数据库的映射关系

语法格式：exec sp_change_users_login 'UPDATE_ONE','用户名','登录名'

Use [数据库名称]
go
sp_change_users_login 'update_one', 'test01', 'test01'

解决方法二、备份方式

如果你的测试库没有其他数据库的话，可以考虑备份的时候把系统数据库也进行备份，这样同时恢复的测试环境的时候，就不会存在孤立用户的问题。

不过一般情况下不推荐，因为测试环境的数据库只有一个情况下比较少。