Confusion about group logins

I am hopelessly confused about how SS2205 resolves the default database and user for group logins.  I have several Windows groups created for different purposes and windows users that are members of those groups. Some users are members of more than one group.

In SS2205, I have corresponding logins for each group with default databases assigned.  And in the appropriate DB's I also have created users for the corresponding groups.  I have not created any logins or users for the windows user accounts--only at the group level.

With some hypothetical names, it looks like this:

Windows Groups      Members
--------------------     ------
Oak\AccountMgr      user1, user2, user3
Oak\CustomerRep    user1, user5
Oak\Cashier            user1, user3, user4
Oak\Billing               user1, user2

SS205
Logins                    Default Database
----------------------  -------------
Oak\AccountMgr      Customers
Oak\CustomerRep    Customers
Oak\Cashier             Billing
Oak\Billing               Billing

Database                Users
---------------------   ----------------------
Customers              Oak\AccountMgr,Oak\CustomerRep,Oak\Cashier
Billing                     Oak\Cashier,Oak\Billing  

So, my questions are about how the default database and user is resolved when there is multiple group membership.  For example, when user1 logs in, what is the basis for choosing billing as the default database? (Why not Customers?) Similarly, what is the basis for choosing Oak\Cashier as the user? (Why not Oak\Billing?)

Finally, after the logon process is complete, then I am sometimes surprised which default database and database user was chosen.  Sometimes, I can't figure out how it got what it got.  In fact, (I swear) sometimes I get a default database that is NOT assigned to ANY logins whatsoever.  Is that possible?  Is there any command or SSMS trick I can use to display which group login was applied as the connection took place?  





Start Free Trial