I created a login for a user using his windows user then I created a login for an AD group in which the same user is a member. I did NOT give any access to the personal login any access to the DB and did not add this login to any server or DB fixed roles. I gave the AD group access to the DB. I have then DISABLED the 1st login (the one using the Windows user) and you know what? The user lost access to the DB!!! When I re-enabled the login (the one using the Windows user) the user again had access to the DB. From this observation I conclude: .) For the authentication when connection to the server, SQL Server either gives precedence to the personal login over the AD group login OR it simply scans the syslogins table to find the first login (which is either the user's personal login or an AD group in which the user is a member) .) After the successful authentication on the instance level, the resulting permissions on the DB is the cumulative permissions of the personal login + of all the AD groups the user is member of.
The first part of this observation does not seem right to me at all. Is this a bug?
Has anyone observed the same behaviour?
TO CLARIFY: The personal login was NOT mapped to the DB at all. The login based on the AD group WAS mapped to the DB and had db_datareader fixed DB role assigned to the user. Still, when I disabled the personal login the user lost all access to the DB. This is IMHO an important point. I did not assign DENY permission to the personal login, I simply disabled it. Therefore my expectation was that the user would continue to have access to the DB through his group membership. But that wasn't the case and that doesn't seem right to me.