我在 SSMS 版本 14、18 和 19 中遇到了一个问题,加载任何给定登录名的用户映射需要很长时间才能加载,有时长达一个小时,或者根本不加载并且点击没有响应。
有没有人以前经历过这种情况和/或能够提供一些解决此问题的指导?
我已经尝试过以下方法:
- 确认是AD登录还是SQL登录都没有关系
- 将 SQL Server 版本从 2016 年升级到 2022 年
- 将 SSMS 版本从 14 升级到 18 和 19
- 检查是否有任何 SQL 块(没有)
- 尝试了多个不同的登录(他们似乎都经历过这个)
此 SQL 实例上有 25 个数据库,总共大约有 50 个登录。
不幸的是,这个问题使得在不编写一些 SQL 查询的情况下管理用户安全和数据库映射变得异常困难。
任何支持或指导将不胜感激!
干杯,
编辑 - 我已经运行了 SQL 跟踪并确定以下查询是违规者:
USE [database_name]
SELECT
u.name AS [Name],
CAST(CASE dp.state WHEN N'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS [HasDBAccess],
ISNULL(u.default_schema_name,N'') AS [DefaultSchema]
FROM
sys.database_principals AS u
LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id
and dp.type = @_msparam_0
WHERE
(u.type in ('U', 'S', 'G', 'C', 'K' ,'E', 'X'))
and
(ISNULL(suser_sname(u.sid),N'')=@_msparam_1)
它在选择要运行的数据库后运行,并且是要运行的第四个查询。
编辑2 - 特别是这部分导致了问题,我不知道为什么:
and
(ISNULL(suser_sname(u.sid),N'''')=@_msparam_1)
编辑3 - 我很确定它是“suser_sname”函数,已经针对不同的数据库和不同的登录进行了测试。它只是挂起,永远不会完成,迫使我关闭 SQL。
任何进一步的指导将不胜感激,谢谢。
编辑 4 - 进一步的上下文,对最初省略此信息表示歉意。
此问题发生在单独的域(我们的 DEV 环境)上。这是我们的生产数据库恢复的地方,并且作为恢复的一部分,关联的 SQL 登录名将根据需要更新以在 DEV 域上工作。
我想知道是否是某些孤立的 SQL 登录导致了此问题,并且 sys.database_principals 中的 SID 不匹配。是的,存在一些孤立的登录,但在使用 sp_change_users_login 修复这些登录后,此问题仍然出现。
TL;DR - 确保在将数据库从一个 Windows 域恢复到另一个 Windows 域时进行强大的用户/登录清理!
完整答案:正如我的问题中提到的,问题出在我们的 DEV 环境中,其中数据库是从 PROD 环境中恢复的。
当我们这样做时,我们运行一个刷新脚本来更新用户并登录到其特定的 Windows 环境。例如,当从 Prod 域刷新到 Dev 域时,PROD\L.Moy 变为 DEV\L.Moy。
此刷新脚本不执行的操作是考虑并删除不需要的用户和登录名。非必需的用户和登录名仍包含其 PROD 域作为 Windows 用户名的一部分。例如,在我们的 DEV SQL Server 中,您仍然可以找到用户 PROD\foobar 作为数据库的用户,尽管不需要。
这对我来说意味着我有大约 40 个 PROD Windows SQL 用户和 25 个 DB,而我们的 DEV 环境不需要这些用户。
因此,请记住上述内容,当您针对登录单击“用户映射”时,它会运行一个名为“SUSER_SNAME”的系统函数。当我专门针对 PROD 用户的 SID 运行此函数并在我们的 DEV 环境中登录时,每个只花费了一分钟多一点的时间。如果您使用 SID 运行“SUSER_SNAME”以进行应该在 DEV 中的登录,则它会立即完成。
因此,这里的解决方案是从 DEV 环境中的所有数据库中实际清除所有实际上不需要的登录名和用户。
然后,当针对登录选择“用户映射”时,它会立即完成,根本没有性能问题。
感谢@Stephen Morris - Mo64 的指导和建议。