用户无法访问数据库，因为他属于多个 AD 组

我们有一个 SQL Server 2016，上面有 20 个不同的数据库，供公司内的不同应用程序和不同团队使用。Active Directory 中有用户组，这些用户组将负责某些项目的员工组合在一起。问题是一名员工可以同时成为多个团队的成员。同一个人可以属于多个不同的 AD 组。

一位用户抱怨他无法访问某个数据库。当他尝试刷新查询该数据库的 Power BI 报告时，他收到一条错误消息“我们无法使用提供的凭据进行身份验证”。

下面是一张简化的图片。一台服务器有两个数据库：DB1和。SQL Server 中DB2有两个 AD 组App_Excel_Reporter和以及两个相应的登录名：DB2_User

CREATE LOGIN [LegacyDomain\App_Excel_Reporter] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

CREATE LOGIN [Domain\DB2_User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

不确定这是否重要，但该服务器是主公司收购的另一家公司的旧服务器。因此，这台 SQL Server 计算机位于域中LegacyDomain。

两个 AD 组LegacyDomain\App_Excel_Reporter都有Domain\DB2_User自己的用户集，但一个用户同时Jack.Universal属于这两个 AD 组。

属于 的用户App_Excel_Reporter应该有权访问DB1。 属于 的用户DB2_User应该有权访问DB2。

其中DB1有一个用户映射到相应的登录名：

USE [DB1]
GO
CREATE USER [LegacyDomain\App_Excel_Reporter] FOR LOGIN [LegacyDomain\App_Excel_Reporter] WITH DEFAULT_SCHEMA=[dbo]
GO

其中DB2有一个用户映射到相应的登录名：

USE [DB2]
GO
CREATE USER [Domain\DB2_User] FOR LOGIN [Domain\DB2_User] WITH DEFAULT_SCHEMA=[dbo]
GO

当用户Jack.Universal尝试刷新 Power BI 时，他会在笔记本电脑上登录 Windows，并且Domain\Jack.Universal我在 SQL Server 日志中看到具有相同时间戳的这些消息：

Login succeeded for user 'Domain\Jack.Universal'. Connection made using Windows authentication, [CLIENT: <ip address>]
Error: 18456, Severity: 14, State: 38.
Login failed for user 'Domain\Jack.Universal'. Reason: Failed to open the explicitly specified database 'DB2'. [CLIENT: <ip address>]

当我跑步时

xp_logininfo 'Domain\Jack.Universal', @option = 'all'

表明

+-----------------------+------+-----------+-----------------------+---------------------------------+
|     account name      | type | privilege |   mapped login name   |         permission path         |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB2_User                 |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB3_User                 |
+-----------------------+------+-----------+-----------------------+---------------------------------+

（是的，有两个以上的数据库，并且该用户属于三个 AD 组）

如果我跑

xp_logininfo 'Domain\Jack.Universal'

如果没有选项“all”，则只返回第一行：

+-----------------------+------+-----------+-----------------------+---------------------------------+
|     account name      | type | privilege |   mapped login name   |         permission path         |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------+------+-----------+-----------------------+---------------------------------+

Domain\Jack.Universal因此，当 Windows 用户登录到 SQL Server 时，SQL Server似乎会选择登录LegacyDomain\App_Excel_Reporter来让该用户进入，但是当它尝试访问数据库时，DB2此尝试会失败，因为登录LegacyDomain\App_Excel_Reporter仅映射到中的用户DB1。

“明确指定的数据库”一定是由于 Power BI 的连接字符串明确指定了DB2。

我们如何配置，以便属于两个 AD 组的用户能够访问两个数据库？仅属于一个 AD 组的用户只能访问相应的数据库。

我不是域管理员，但我可以向他们询问任何需要的信息。公司没有 DBA，我只是一名对 SQL Server 略知一二的程序员。

我挖得更深了一点。当我跑

EXEC xp_logininfo 'Domain\DB2_User', @option = 'members'

我获得了该组中的用户列表：

+-----------------------+------+-----------+-----------------------+-----------------+
|     account name      | type | privilege |   mapped login name   | permission path |
+-----------------------+------+-----------+-----------------------+-----------------+
| Domain\user1          | user | user      | Domain\user1          | Domain\DB2_User |
| Domain\user2          | user | user      | Domain\user2          | Domain\DB2_User |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB2_User |
+-----------------------+------+-----------+-----------------------+-----------------+

并且用户也Domain正如预期的那样。

当我跑步时

EXEC xp_logininfo 'LegacyDomain\App_Excel_Reporter', @option = 'members'

我明白了：

+-----------------------------+------+-----------+-----------------------------+---------------------------------+
|        account name         | type | privilege |      mapped login name      |         permission path         |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| LegacyDomain\user3          | user | user      | LegacyDomain\user3          | LegacyDomain\App_Excel_Reporter |
| LegacyDomain\user4          | user | user      | LegacyDomain\user4          | LegacyDomain\App_Excel_Reporter |
| LegacyDomain\Jack.Universal | user | user      | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+

这里权限路径在LegacyDomain，账户名也在LegacyDomain，但是上面的结果中xp_logininfo 'Domain\Jack.Universal'账户名在Domain，但是权限路径在LegacyDomain。

再次：

EXEC xp_logininfo 'Domain\Jack.Universal', @option = 'all'

+-----------------------+------+-----------+-----------------------+---------------------------------+
|     account name      | type | privilege |   mapped login name   |         permission path         |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB2_User                 |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB3_User                 |
+-----------------------+------+-----------+-----------------------+---------------------------------+

EXEC xp_logininfo 'LegacyDomain\Jack.Universal', @option = 'all'

+-----------------------------+------+-----------+-----------------------------+---------------------------------+
|        account name         | type | privilege |      mapped login name      |         permission path         |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| LegacyDomain\Jack.Universal | user | user      | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+

Active Directory 中一定存在一些神奇的功能，可以将用户从旧域映射到主域。