Find sysadmins Hiding in Active Directory with PowerQuery
Chances are if you are using SQL Server in an enterprise environment you are probably using Active Directory groups to setup and maintain permissions, but how as a DBA do you know who the users are behind the groups and many there actually are? This is especially important for the sysadmin permission.
In this article I’m going to show how to use PowerQuery to find all the users in Active Directory that have the sysadmin permission. As Brent Ozar puts it “these are the people that can get you fired”.
NB: I will be using the November update for PowerQuery.
Get a list of sysadmins
Open Excel 2013 and on the PowerQuery tab select “From Data” and then “From SQL Server Database”. In the window that is then displayed, enter your server name, master for the database then the following SQL:
Click OK to load a preview into the Query Editor.
Split the name column, using \ as the custom separator. Then rename the columns to: Domain, GroupName, IsDisabled, Permission. Remove the type_desc column.
You should end up with something like this:
Click Apply & Close to load the results into Excel.
Creating a PowerQuery Function using Active Directory
The next step is to create a PowerQuery function which will query Active Directory for a given domain and group and return the members.
I found the easiest way to create this as a function was to go through all the steps for the first group then using the Advanced Query Editor to turn the M code into a generic function, which could then be reused.
This was the function that I ended up creating:
You can paste this directly into a blank query, and then test it by using the Invoke button in the Query Editor.
If you do test the function using the invoke make sure you remove this step, then click Apply & Close.
Combining the sysadmin Active Directory groups and the PowerQuery function
In this final step the list of Active Directory groups with the sysadmin permission is combined with the function created above.
Before this can be done a mapping table needs to be created for the domain name that SQL Server outputs and what it actually is. For example, as far as Active Directory is concerned your domain might be mydomain.local or mydomain.priv, however SQL Server is only going to say mydomain\groupname.
So, I created a very simple table in an Excel sheet with four columns (Domain, FullyQualifiedDomain, OU, DC). Which looks something like this:
NB: To merge this table with the output from the sysadmins query the value in the Domain columns need to match exactly.
With this table selected, I clicked the “From Table” option in the PowerQuery ribbon to bring up the Query Editor so that I could merge this table with the results of the sysadmin query.
To merge this table, click the Merge button, then select the query which corresponds to the sysadmins in the drop down, then click the “Domain” column heading in each table.
Click OK and a new column appears, I then expanded the column, with all the new columns of the table selected.
To use the GetADUsers function, add a custom column and call the function
Click OK, then expand the newly added column.
I then cleaned things up a bit by renaming some of the columns and removing the unnecessary ones. The final result ends up like this:
Click Apply & Close to load the result into Excel and send it off to your boss / security team :)