Zombie AD Groups


September 21, 2015 by Kenneth Fisher

I spent something like 2 hours today trying to figure out how a particular user had access to one of my databases. It’s one of the strangest things I’ve ever seen while playing with SQL Server security. Here’s how it plays out:

You have a windows group called [Kenneth-Laptop\Dwarf] with a member called [Kenneth-Laptop\Dopey]. You grant Dwarf SELECT permissions on a database.

USE Test;
CREATE USER [Kenneth-Laptop\Dwarf] FROM LOGIN [Kenneth-Laptop\Dwarf];
GRANT SELECT TO [Kenneth-Laptop\Dwarf];

Dopey can now query a table in the Test database. Some time later you remove the login for Dwarf.

DROP LOGIN [Kenneth-Laptop\Dwarf];

So at this point Dwarf is an orphaned user in the Test database and Dopey doesn’t have any direct access. And would you believe Dopey still has SELECT access? For some reason even though the group is orphaned it’s still active.

The takeaway here (aside from an odd security trick) is that if you are removing a server principal (login) you need to remove all of the associated database principals (users).

4 thoughts on “Zombie AD Groups

  1. Peter says:

    Perhaps I am confused, if you are adding Dopey via the AD group Dwarf as a database user, why are you also adding Dopey as a user separately?

    • Mostly for demonstration purposes. I could just as easily have added a second group called Disney. It’s not unusual for someone to have permissions based on their ID and be a member of an AD group that provides other permissions as well though.

  2. andreaallred says:

    Did Dopey have access to the server through another login?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: