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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT
  spm.name, sp.is_disabled, sp.name, spm.type_desc
FROM
  sys.server_role_members srm,
  sys.server_principals sp,
  sys.server_principals spm
WHERE
  sp.name = 'sysadmin' AND
  sp.principal_id = srm.role_principal_id AND
  spm.principal_id = srm.member_principal_id AND
  spm.type_desc = 'WINDOWS_GROUP';

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

let
    GetADUsers = (domainName as text, groupName as text, ou as text, dc as text) =>
let
    Source = ActiveDirectory.Domains(domainName),
    theDomain = Source{[Domain=domainName]}[#"Object Categories"],
    group = theDomain{[Category="group"]}[Objects],
    InsertedCustom1 = Table.AddColumn(group, "distinguishedNameLower", each Text.Lower([distinguishedName])),
    ABC = InsertedCustom1{[distinguishedNameLower=Text.Lower(Text.Replace(Text.Replace(Text.Replace("CN={GROUPNAME},OU=Groups,OU={OU},DC={DOMAIN},DC=priv", "{GROUPNAME}", groupName), "{DOMAIN}", dc), "{OU}", ou))]}[group],
    member = ABC[member],
    TableFromList = Table.FromList(member, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    DEF = Table.ExpandRecordColumn(TableFromList, "Column1", {"displayName", "distinguishedName"}, {"Column1.displayName", "Column1.distinguishedName"}),
    InsertedCustom = Table.AddColumn(DEF, "Custom", each Text.Contains([Column1.distinguishedName],"OU=Users")),
    RenamedColumns = Table.RenameColumns(InsertedCustom,{{"Custom", "IsUser"}}),
    InsertedCustom4 = Table.AddColumn(RenamedColumns, "Custom.3", each Text.Range([Column1.distinguishedName], Text.PositionOf([Column1.distinguishedName],"DC=")+3,Text.Length([Column1.distinguishedName])-Text.PositionOf([Column1.distinguishedName],",DC=priv")+4)),
    RenamedColumns2 = Table.RenameColumns(InsertedCustom4,{{"Custom.3", "Domain"}}),
    RemovedColumns1 = Table.RemoveColumns(RenamedColumns2,{"Column1.distinguishedName"}),
    ReorderedColumns = Table.ReorderColumns(RemovedColumns1,{"Column1.displayName", "Domain", "IsUser"}),
    RenamedColumns1 = Table.RenameColumns(ReorderedColumns,{{"Column1.displayName", "DisplayName"}})
in
    RenamedColumns1
in
    GetADUsers

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 :)

Useful Resources:

http://office.microsoft.com/en-us/excel-help/power-query-specifications-and-limits-HA104054432.aspx?CTT=5&origin=HA104003813

http://office.microsoft.com/en-us/excel-help/power-query-specifications-and-limits-HA104054432.aspx?CTT=5&origin=HA104003813

http://office.microsoft.com/en-us/excel-help/start-page-HA104003813.aspx?WT.mc_id=Blog_PBI_Features_PowerQuery

http://office.microsoft.com/en-us/excel-help/power-query-formula-categories-HA104122363.aspx?CTT=5&origin=HA104003813