解决SQL Server中的孤立用户问题

在将数据库迁移到新的服务器后,可能会遇到一个常见的问题:尽管数据库用户存在于恢复的数据库中,但登录信息却丢失了,这被称为“孤立用户”问题。这种情况通常发生在更换服务器或重建/恢复旧版本的master数据库时,因为用户登录信息存储在master数据库的syslogins表中。如果用户尝试登录服务器,但登录信息不存在,他们将收到“登录失败”的错误。如果登录信息存在,但master数据库中的syslogins表和用户数据库中的sysusers表中的SID(安全标识符)不同,用户可能会在用户数据库中获得预期之外的权限。

问题详情

用户登录信息存储在master数据库的syslogins表中。当更换服务器或通过重建或恢复旧版本的master数据库更改此信息时,信息可能与创建用户数据库转储时不同。如果登录信息不存在,用户在尝试登录服务器时将收到“登录失败”的错误。如果登录信息存在,但master数据库的syslogins表和用户数据库的sysusers表中的SID不同,用户可能会在用户数据库中获得预期之外的权限。

如何修复

修复这个问题的最简单方法是从恢复的数据库中删除用户,然后创建并设置用户及其对应的数据库权限。如果用户拥有数据库中的架构,将无法删除该用户。在这种情况下,可以使用特殊的存储过程sp_change_users_login。

EXEC sp_change_users_login [ @Action = ] ‘action’ [ , [ @UserNamePattern = ] ‘user’ ] [ , [ @LoginName = ] ‘login’ ] [ , [ @Password = ] ‘password’ ]

以下是该过程可以执行的操作列表:

  • Auto_Fix:将当前数据库中的sysusers表中的用户条目链接到sysxlogins中同名的登录。应该检查Auto_Fix语句的结果,以确认确实建立了正确的链接。在安全敏感的情况下避免使用Auto_Fix。
  • Report:列出当前数据库中未链接到任何登录的用户及其对应的安全标识符(SID)。user、login和password必须为NULL或未指定。
  • Update_One:将当前数据库中指定的用户链接到登录。登录必须已经存在。必须指定user和login。password必须为NULL或未指定。

要执行操作,首先需要选择包含问题的数据库。以下是可以执行的一些操作:

-- 列出孤立用户 EXEC sp_change_users_login 'Report'

如果已经创建了一个具有相同登录信息的SQL服务器,并且希望将其映射到数据库用户:

-- 自动修复孤立用户 EXEC sp_change_users_login 'Auto_Fix', 'user'

MSDN指出,这将现有数据库用户映射到SQL Server登录。

使用ALTER USER命令

sp_change_users_login功能将在未来的MicrosoftSQL Server版本中移除。避免在新开发工作中使用此功能,并计划修改当前使用此功能的应用。改用ALTER USER。

ALTER USER userName WITH [ ,...n ] <set_item> ::= NAME = newUserName | DEFAULT_SCHEMA = schemaName | LOGIN = loginName
  • userName - 指定用户在当前数据库中的识别名称。
  • LOGIN = loginName - 通过更改用户的安全标识符(SID)以匹配登录的SID,重新映射用户到另一个登录。
  • NAME = newUserName - 指定此用户的新名称。newUserName在当前数据库中必须不存在。
  • DEFAULT_SCHEMA = schemaName - 指定服务器解析此用户的对象名称时首先搜索的第一个架构。
ALTER USER UserName WITH LOGIN = UserName
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485