如何修复SQL Server数据库孤立用户

在日常企业的运维中，不同时期都会遇到设备更换，架构改造升级等事项。对于上层的系统和应用来说，则会随着运维要求，逐步进行迁移或升级，数据库系统也包含在其中。通常在数据库迁移或升级中，都会采用备份还原的方式，既安全又效率。但是除了迁移数据库本身，而实例级的一些对象，是不会随着数据库一起被迁移的，如：登录名、权限、作业等问题。由于账号和权限等丢失，在环境上，会造成很多的麻烦。

本文将提供详细的解决方法，针对如何修复在迁移或升级后，数据库用户的映射和权限等。

 

【正文】

1       孤立用户的现象

l  产生场景：

1)     数据库发生迁移后，User找不到其SID对应的Login存在；

2)     数据库镜像、日志传送，主备服务器切换后，登录名不存在。

l  产生原因：

3)     Login属于实例级对象，User属于数据库级对象；

4)     当数据库通过备份还原、分离附加或主备切换等方式发生迁移，User跟随数据库进行了迁移，而Login仍保留在源实例的master库中。

 

 

2       孤立用户产生的过程

1.     在数据库服务器上创建一个测试账号，“canwaytest”。

2.     给这个账号设置指定数据库的映射权限，如：人事管理系统。

3.     在登录名中，找到已经创建成功的新账号“canwaytest”。

4.     检查数据库“人事管理系统”，新建的账号在指定的数据库下，生成了同名对应的用户。

5.     通过新登录名“canwaytest”连接数据库实例，成功对指定的数据库，并能对数据库中的表进行增、删、改、查，说明新的登录名有该数据库的操作权限。

6.     通过备份还原的方式，我们将数据库“人事管理系统”迁移升级至SQL2014的服务器上。

7.     尝试用旧的账号“canwaytest”连接到新的数据库实例上。

8.     连接失败。

9.     因为在新的数据库服务器上，登录名中，是没有原来的“canwaytest”账号。

10.  尝试在新的数据库服务器上创建同名的账号。

11.  成功连接到新实例上，但是由于缺少权限，无法访问“人事管理系统”数据库。

12.  尝试给新的账号“canwaytest”授权。

13.  授权和映射失败，因为数据库的用户中已经存在一个同名的旧用户。

14.  通过命令检查，可以发现，数据库“人事管理系统”中的用户“canwaytest”，是孤立用户。

15.  因为两者底层的SID号都不一样，所以无法关联在一起，必然发生冲突。假如：这个账号是应用服务器和数据库服务器的连接账号。在迁移升级后的新服务器将无法连通，系统也无法正常上线。

16.  以上的测试，就是孤立产生的过程和原因，以及对生产的影响。

 

 

 

 

 

 

 

 

 

3       解决方法一

1.     重新创建登录账号，修复用户到账号映射，保持用户名和登录名同名。

2.     首先通过命令，检查出当前数据库下面是否存在孤立用户。

--报告数据库中的孤立用户

Exec sp_change_users_login 'Report'

3.     在登录名中，创建与用户同名的登录名，可以使用命令，也可以通过图形界面创建。

4.     通过命令，将用户重新映射到新的登录名上。（Auto_Fix自动匹配两者底层的SID，后面填入同名的登录名和用户名）

--将user映射到新Login的SID

Exec sp_change_users_login 'Auto_Fix',canwaytest

5.     重新检查新创建的登录名属性，登录名和原用户的映射、权限关系完全恢复。

6.     通过修复的账号连接新数据库，能正常执行操作，孤立用户修复。

4       解决方法二

1.     简单粗暴的删除原来数据库下面的用户。

2.     在登录名中，重新创建新的登录名，并映射给指定的数据库，分配相应的权限。