User can't access a database because he belongs to multiple AD groups

We have an SQL Server 2016 that has 20 different databases on it that are used by different applications and different teams in the company. There are groups of users in Active Directory that combine employees that work on certain projects. The problem is that an employee can be part of several teams at the same time. The same person can belong to several different AD groups. One user complained that he could not access a certain database. When he was trying to refresh the Power BI report that queries that database he was getting an error message "We couldn't authenticate with the credentials provided". Here is a simplified picture. A server has two databases: DB1 and DB2. There are two AD groups App_Excel_Reporter and DB2_User and two corresponding logins in the SQL Server: CREATE LOGIN [LegacyDomain\App_Excel_Reporter] FROM WINDOWS WITH DEFAULT_DATABASE=[master] CREATE LOGIN [Domain\DB2_User] FROM WINDOWS WITH DEFAULT_DATABASE=[master] Not sure if this is important, but this server is a legacy server that was part of another company that the main company bough. So, this SQL Server computer is in domain LegacyDomain. Two AD groups LegacyDomain\App_Excel_Reporter and Domain\DB2_User have their own sets of users, but one user Jack.Universal belongs to both AD groups. Users that are part of App_Excel_Reporter should have access to DB1. Users that are part of DB2_User should have access to DB2. In DB1 there is a user mapped to the corresponding login: USE [DB1] GO CREATE USER [LegacyDomain\App_Excel_Reporter] FOR LOGIN [LegacyDomain\App_Excel_Reporter] WITH DEFAULT_SCHEMA=[dbo] GO In DB2 there is a user mapped to the corresponding login: USE [DB2] GO CREATE USER [Domain\DB2_User] FOR LOGIN [Domain\DB2_User] WITH DEFAULT_SCHEMA=[dbo] GO When a user Jack.Universal tries to refresh the Power BI he is logged into Windows on his laptop as Domain\Jack.Universal and I see these messages in SQL Server log with the same timestamp: Login succeeded for user 'Domain\Jack.Universal'. Connection made using Windows authentication, [CLIENT: ] Error: 18456, Severity: 14, State: 38. Login failed for user 'Domain\Jack.Universal'. Reason: Failed to open the explicitly specified database 'DB2'. [CLIENT: ] When I run xp_logininfo 'Domain\Jack.Universal', @option = 'all' it shows +-----------------------+------+-----------+-----------------------+---------------------------------+ | 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 | +-----------------------+------+-----------+-----------------------+---------------------------------+ (yes, there is more than two databases and this user belongs to three AD groups) If I run xp_logininfo 'Domain\Jack.Universal' without option "all", then only the first row is returned: +-----------------------+------+-----------+-----------------------+---------------------------------+ | account name | type | privilege | mapped login name | permission path | +-----------------------+------+-----------+-----------------------+---------------------------------+ | Domain\Jack.Universal | user | user | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter | +-----------------------+------+-----------+-----------------------+---------------------------------+ So, it seems that when a Windows user Domain\Jack.Universal logs into the SQL Server, the SQL Server picks login LegacyDomain\App_Excel_Reporter to let the user in, but when it tries to access a database DB2 this attempt fails because login LegacyDomain\App_Excel_Reporter is mapped only to the user in DB1. "the explicitly specified database" must be due to Power BI's connection string which explicitly specifies DB2. How do we configure this so, that a user who belongs to both AD groups would have access to both databases? Users who belong only to one AD group should have access only to the corresponding database. I am not a domain admin, but I can ask them whatever is needed. The company doesn't have a DBA, I'm just a programmer who knows a thing or two about SQL Server. ---------------- I dug a bit deeper. When I run EXEC xp_logininfo 'Domain\DB2_User', @option = 'members' I get a list of users in that group: +-----------------------+------+-----------+-----------------------+-----------------+ | 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 | +-----------------------+------+-----------+-----------------------+-----------------+ And the users are in Domain as expected. When I run EXEC xp_logininfo 'LegacyDomain\App_Excel_Reporter', @option = 'members' I get this: +-----------------------------+------+-----------+-----------------------------+---------------------------------+ | 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 | +-----------------------------+------+-----------+-----------------------------+---------------------------------+ Here permission path is in LegacyDomain and account name is also in LegacyDomain, but above in the results of xp_logininfo 'Domain\Jack.Universal' the account name is in Domain, but permission path is in LegacyDomain. Once again: 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 | +-----------------------------+------+-----------+-----------------------------+---------------------------------+ There must be some magic in Active Directory that maps users from legacy domain into the main domain.