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.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: code language, language sql, microsoft sql server, security, sql statements