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.
Best Answer
SQL Server will deny access to a login that has been disabled. This is by design. If it is your intention to allow the person access through the group login, you should simply remove the login that is linked to the Windows account.
SQL Server limits principals' rights such that any rights that have been explicitly denied override rights that have been explicitly or implicitly granted. For instance, if a Windows Account has a login, and is also a member of an Active Directory group that also has a login, the set of rights granted at connection time actually reflect the rights for both the Account login and the Group login. If the group login permits access to a specific resource, but the account login denies access to that resource, then access to that resource is denied.
You may be interested in seeing the SQL Server Database Engine Permissions Poster.