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.
Does this explain the language that you would see in the Audit Login Event for a TCP/IP connection?
Also would this apply to a domain account that uses ADO.net connections, specifying the Database in the connection string. e.g.
If on SQL Server the Domain account DOMAIN\TestAccount is in Groups DOMAIN\Group1 and DOMAIN\Group2
DOMAIN\Group1 has access to DB1 with Language = English
DOMAIN\Group2 has access to DB2 witch Language = British English
In the ADO.net connection we use a Trusted Connection (code is executed under the DOMAIN\TestAccount account) and the database in the connection string was DB1, is it possible that it could take the language of DOMAIN\Group2 based on your findings, or would it always use DOMAIN\Group1?
Look forward to a reply.
Indeed Neal. The language a login goes by default is shown in the "Audit Login" event as the "set language" session setting. And that is the case regardless of the transport provider the client uses to connect to that instance. So it could be TCP, but also Named Pipes, or Shared Memory.
And yes Neal, this applies regardless of the data access client used (ADO.Net in your case). As explained in the post, if you want to deterministically know that always the same language is assigned to your login you will have to create a login for that account and set on it the default language of your preference.