SQL Server Mysteries: The Case of the Dropped AD Group Login

Moved from: bobsql.com


Dylan (who kindly wrote up the contents of this blog for me) was modifying the tests for Active Directory Login activities.  As Dylan and I reviewed the changes a specific behavior involving Active Directory Group Logins caught our attention.


Imagine you have a group on your domain [CONTOSOgroup] which has a member [CONTOSOuser], and the [CONTOSOgroup] has login permissions to a SQL Server instance. Running “DROP LOGIN [CONTOSOgroup]” we expected that the [CONTOSOuser] would no longer have access to SQL Server.  However, what we observed was:


New connections from [CONTOSOuser] are not accepted Existing connections may