Finding a common AD group for a set of users.

8

December 29, 2014 by Kenneth Fisher

I work for a large organization that over the last few years has been doing a LOT of reorgs. And what comes with reorgs? Lots of security requests. I’m always working to find easier ways to deal with them and I’ve gotten pretty good at it over time. One common security request we receive looks something like this: “Please grant XYZ access to Doc, Dopey, Sleepy and Sneezy.” This is easy enough if we grant each individual permissions directly, but we try to follow the best practice of granting permissions to Active Directory (AD) groups rather than individuals. In the past I would go into AD, find an individual, scan through the dozens of AD groups they belong to, then on to the next individual and compare, and on and on. If you’ve never tried this let’s just say it’s painful.

I learned something new recently that has been a big help. There is a system view called sys.login_token that among other things returns all of the AD groups that the current security context belongs to. So how does that help us? It only returns our windows groups after all. Ahh, but when you add in the magic of EXECUTE AS which allows us to change our security context, we can pull the list for any login. By dumping the information into temp tables we can then compare them and get a single list of AD groups that they all belong to.

-- Collect AD Groups for Kenneth-Laptop\Doc
EXECUTE AS LOGIN = 'Kenneth-Laptop\Doc'
SELECT name INTO #Doc from sys.login_token
WHERE TYPE = 'WINDOWS GROUP'
REVERT

-- Collect AD Groups for Kenneth-Laptop\Dopey
EXECUTE AS LOGIN = 'Kenneth-Laptop\Dopey'
SELECT name INTO #Dopey from sys.login_token
WHERE TYPE = 'WINDOWS GROUP'
REVERT

-- Collect AD Groups for Kenneth-Laptop\Sleepy
EXECUTE AS LOGIN = 'Kenneth-Laptop\Sleepy'
SELECT name INTO #Sleepy from sys.login_token
WHERE TYPE = 'WINDOWS GROUP'
REVERT

-- Collect AD Groups for Kenneth-Laptop\Sneezy
EXECUTE AS LOGIN = 'Kenneth-Laptop\Sneezy'
SELECT name INTO #Sneezy from sys.login_token
WHERE TYPE = 'WINDOWS GROUP'
REVERT

-- Generate a list of AD Groups that all belong to
SELECT name FROM #Doc
INTERSECT
SELECT name FROM #Dopey
INTERSECT
SELECT name FROM #Sleepy
INTERSECT
SELECT name FROM #Sneezy

Now that you have a list of common AD groups you should actually go into AD and check the membership of each group to be sure no one is getting permissions they shouldn’t. Fortunately the common list of groups is probably going to be fairly small and checking for extra members is fairly easy.

Now there are other ways to do this. Andrea Allred(b/t), for example, recently posted about using xp_logininfo to find AD information. This method is great but does require some external (AD) permissions that I don’t actually have at work. On the other hand, if you have those permissions, it can also return all members of a given AD group which can be very helpful and would let us avoid going into AD at all in the above example.

8 thoughts on “Finding a common AD group for a set of users.

  1. wolf says:

    <>

    why is this the best practice? If the DBA is not an AD admin (or if there are other non-DBA AD Admins) what prevents users from being inadvertantly plopped into a resource group that has access to SQL?

    • It can happen but it removes the DBA (partly) from the security business and put’s it back on the AD admins who are typically more versed in the over all security of the company than the DBAs. There does have to be some communication between the AD Admins and the DBAs for it to work though. And trust .. always trust 🙂

  2. davegugg says:

    Hi Kenneth
    Nice solution, very cool. Thank you for sharing.

    If I ask an unrelated question, what do you use to format your SQL code here on WordPress? I’m trying to start a blog here and I’ve been putting all my code in quotes, it looks ugly.

    Thank you

    • Thanks, glad you liked it :).
      Sorry, I’m having to edit this multiple times. It’s very difficult to get it to not treat my blocks as blocks 🙂

      All you have to do with the code is block it out using CODE blocks (put []s around the word CODE). It also has an option “language=sql” that I use for all of my SQL code.

  3. […] scripts to help you understand your security configuration – you can find those here and here. Enjoy watching and let us what you think in the […]

  4. […] really useful right? It is. I frequently use it to find AD information and I also use it to look at sys.fn_my_permissions as a quick way to get a users effective […]

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 1,664 other followers

Follow me on Twitter

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