When a domain user is in multiple Windows groups, and these group logins have a different default databases (and different language) on the same instance, and there is no login for the Windows user account, how does SQL Server decide which database (and which language) the domain user should go by default?
It basically depends on the order in which the SIDs of the groups were returned by the GetTokenInformation API. The first SID in that array with a matching login is the one from where the default database is taken.
In the following test, I used my TESTDOMAIN\NACHO account to login into an instance of SQL Server installed in a host called TEST. The instance has no login created for the account itself, so it resorts to discovering the NT groups that account is a member of.
From all the groups it is a member of, the following three have a login created (stored in system table sysxlgns) in the instance of SQL Server, and this is the order in which they were returned by the aforementioned API. Notice that they don’t come sorted neither by SID nor by name. (The list of SIDs for which there were no matching entries in sysxlgns have been replaced by ellipsis to minimize the space used):
SID is: S-1-5-21-2691281649-1894694337-164649847-1010 (Alias: TEST\OneGroup)
SID is: S-1-5-21-2691281649-1894694337-164649847-1009 (Alias: TEST\AnotherGroup)
SID is: S-1-5-21-2691281649-1894694337-164649847-1031 (Alias: TEST\YetAnotherGroup)
Since the default database for the first one found (TEST\OneGroup) is OneDB (see below), that’s the one it uses.
Elements in sysxlgns, (highlighted those with SIDs matching the ones returned by the API corresponding to the groups the account was a member of). Notice that the one from where the default database is taken, is not necessarily the one with the lower principal_id, neither the one with the one with the lower SID:
name principal_id sid default_database_name
=========== ============ ========================================================= =====================
sa 1 0x01 master
public 2 0x02 NULL
TEST\YetAnotherGroup 269 0x010500000000000515000000F1B269A0C1BDEE70775BD00907040000 YetAnotherDB
TEST\AnotherGroup 270 0x010500000000000515000000F1B269A0C1BDEE70775BD009F1030000 AnotherDB
TEST\OneGroup 271 0x010500000000000515000000F1B269A0C1BDEE70775BD009F2030000 OneDB
Unfortunately, this leaves us in a situation in which, knowing what the first SID will be the one chosen to set the default database for the session is unpredictable.
Therefore, up until now, if you want to deterministically know that always the same default database and default language is assigned to any given Windows account login into SQL Server, you must either (1) make sure that from the Windows groups that account is a member of, only one of the groups has a login created in SQL Server, or (2) create a specific login for that account and set on it the default database and language you want it to be assigned. In the latter case, permissions could still be granted via the logins mapped to the Windows groups, but these two attributes would be inherited from the login mapped to the user account.