SQL Server Logins, and Database Engine Connection States

Earlier today I tried to deprecate an old SQL Login by opening the Login's property Window, navigating to the Status page and then I changed the Permission to connect to databae engine property to deny.

The login was associated with an active directory group and although the group still exists and users are in it, the group (and associated login) are n开发者_高级运维o longer used in SQL Server. Bad idea. Apparently this locked out all of my users that were in that group, even though they were associated with numerous other SQL Logins that had access to the databases they needed.

As soon as I gave the Login Grant access again, my users had access to the Databases Server.

My question is, is there thorough documentation on this behavior and any other gotchas that may arise when disabling/modifying old, deprecated SQL Server accounts?

I have found this MSDN article which is the reference to this property, however, it doesn't mention that it would lock out any user associated with the Login. Is there more information available? I still have quite a bit of work to do and I'd prefer to not lock my user's out of the system again because of ignorance.

Simply remove (REVOKE) permissions rather then DENY.

This means there is "no permission" rather then "explicit DENY": DENY overrides other permissions. Because there are other permissions, normal access will be unaffected.

Saying that, if you DROP it, it's very simply to recreate it...

CREATE LOGIN [mydomain\mygroup] FROM WINDOWS;
GRANT CONNECT SQL TO [mydomain\mygroup]

In both of these cases (REVOKE or DROP), you may find that some folk can no longer access SQL Server (which is different to DENYing everyone in that group) and is the lesser evil