Querying Active Directory from SQL Server

At many companies, access to employees PC resources (USB drive, CD ROM drive, root of C: etc) is managed by Group Policy. Sometimes Group Policy with the requisite settings is applied to a Security Group so members of that group inherit the settings of that GPO. So you may end up with many different policies applied to many different groups.

This is a straight forward easy way to manage GPOs - if you want to know what access a user has to their PC simply look at their group membership.

Being a database type person it occurred to me, it would be nice if we could query Active Directory at regular intervals to see who has what permissions to their PC (for security auditing), populate a table with the results and perhaps point Excel at the table to create a nice report for management. (I won't cover the Excel part in this post but there is very little to it)

I looked (Googled) various different ways this could be achieved but nothing quite did it the way I wanted so in the end had to come up with my own solution.

A popular solution can be found at mssqltips.com using a linked server to a Domain Controller and ADSI but the problem I found with it is that I couldn't find an easy way to drill down through AD to find the group objects - If all the groups you are interested in are in the same OU then it worked fine but if they were spread about in different OUs, some at the same level of the tree and others off to one side or the other then you had to write separate code to address each OU. Which is fine until someone moves a group to a different OU due to tidy up and then your query breaks.

Other solutions suggested using WMI but I had no luck there either. So in the end I went for the command line tools DSQuery and DSGet called by xp_CmdShell. With these tools you don't have to use an LDAP path, you just run them as you would any other command line tool from with SQL Server. Both tools are available in the Remove Server Administration Tools.

There are several issues with the Remove Server Administration Tools approach:

1. Some shops don't like xp_CmdShell to be enabled due to a perceived security issue (not a biggy IMHO)
2. The account you run the tools under needs permissions to Active Directory
3. DSGet and DSQuery have to be installed on the SQL Server you will be querying from. See here for instructions on how to do it.
4. The output from DSGet and DSQuery is nasty and requires a lot of cleaning up before it fits nicely into fields in a table. A big part of the query below is making the results returned usable.

Your business may have a policy for the first three points which may restrict your use of this script, but assuming you are good to go feel free to use it (please read the comments in line for further info and as usual I take NO responsibility for any issues AT ALL if you execute the script on ANY server - but it should be fine!). The guts of the script are taken from this command:

dsquery group -name GG-C_Drive_Access | dsget group -members -expand -c | dsget user -samid -display -dept -c | Find /V "dsget succeeded" | Find /V "samid" | Find /V "_"

What this is saying is query the group GG-C_Drive_Access and pipe the results to dsget group and get the member attributes samid, display and dept. This is piped to Find to not return the lines that contain dsget succeeded, samid and the underscore (you may have different/additional requirements).

The query depends on a function called ufn_Split

/******************************************************************************************

Name    : Show Groups, members and departments from Active Directory.sql

Author  : Paul Hewson (IT Operations DBA)

Purpose : This script uses dsquery and dsget (from the Remote Administration

                Tools) to query the membership of the specified Global Security Groups. These groups determine the rights a user has to their PC. 

-------------------------

Version    :     1.0

Date       : 02 May 2014

Info       : Initial version

Depends on : ufn_split

*******************************************************************************************/

USE DBA;

GO

/*Drop the tables that will be used if they exist*/

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#Groups')) DROP TABLE #Groups

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#Temp')) DROP TABLE #Temp

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#GroupsToCheck')) DROP TABLE #GroupsToCheck

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#GroupMembershipTemp')) DROP TABLE #GroupMembershipTemp

IF EXISTS (SELECT 1 FROM DBA..sysobjects WHERE id=OBJECT_ID('DBA..GroupMembership')) DROP TABLE GroupMembership

/*Create the tables */

CREATE TABLE #Groups (GroupName VARCHAR(100), Member VARCHAR (400))

CREATE TABLE #Temp (Member VARCHAR (400))

CREATE TABLE #GroupMemberShipTemp(ID INT IDENTITY (1,1),GroupName VARCHAR(50), Field VARCHAR(100), Member VARCHAR(100), Polldate DATETIME) 

CREATE TABLE GroupMemberShip(ID INT, GroupName VARCHAR(50), Username VARCHAR(100), Fullname VARCHAR(100), Dept VARCHAR(100), Polldate DATETIME)

/*The list of Security Groups to check. You may add/delete from this list as required*/

CREATE TABLE #GroupsToCheck(ID INT IDENTITY (1,1), GroupName VARCHAR(50))

INSERT #GroupsToCheck

SELECT 'GG-C_Drive_Access' UNION ALL

SELECT 'GG-D_Drive_Access' UNION ALL

SELECT 'GG-Allow_Command_Prompt_Access' UNION ALL

SELECT 'GG-Local_Admin'

/*Cursor to populate the #groups table with the unformatted  output of dsquery*/

DECLARE @GroupName VARCHAR(100)

DECLARE @Member VARCHAR(400)

DECLARE @SQL VARCHAR(MAX)

DECLARE curADGroups CURSOR FOR

SELECT

       GroupName

FROM

       #GroupsToCheck

      

OPEN curADGroups

FETCH NEXT FROM curADGroups INTO @Groupname

WHILE @@FETCH_STATUS = 0

       BEGIN

                TRUNCATE TABLE #temp

               

                SET @SQL = 'EXEC MASTER..XP_CMDSHELL ''dsquery group -name "'

                SELECT @SQL = @SQL + @groupName

                SET @SQL = @SQL + '" | dsget group -members -expand -c | dsget user -samid -display -dept -c | Find /V "dsget succeeded" | Find /V "samid" | Find /V "_"'''

                --PRINT @SQL

                INSERT #temp

                EXEC (@SQL)

                INSERT #groups

                SELECT @GroupName, Member

                From #temp

              FETCH NEXT FROM curADGroups INTO @GroupName

       END

CLOSE curADGroups

DEALLOCATE curADGroups

/*The unformatted output from dsquery includes the Username, Fullname and Department of an AD user

in one column separated by two spaces. This query splits these values into separate rows with an ordinal number

that is renamed appropriately.*/  

INSERT #GroupMemberShipTemp

SELECT 

          GroupName

          ,CASE s.ordinal WHEN 1 Then 'UserName' WHEN    2 Then 'FullName' WHEN 3 THEN 'Dept' END AS Field

          ,s.StringValue

          ,GETDATE()

FROM #groups u

          CROSS APPLY master.dbo.ufn_split(REPLACE(REPLACE(REPLACE(RTRIM(lTRIM(u.member)),'  ','<>'),'><',''),'<>',','), ',') s

/*Pivot the output from the previous query into the correct columns*/

;WITH groupCTE AS

(

SELECT

    ID

    ,GroupName

    ,(SELECT member FROM #GroupMemberShipTemp g2 WHERE field = 'UserName' AND g2.ID = g.ID ) AS UserName

    ,(SELECT member FROM #GroupMemberShipTemp g3 WHERE field = 'FullName' AND g3.ID = g.ID + 1 ) AS FullName

    ,(SELECT member FROM #GroupMemberShipTemp g4 WHERE field = 'Dept' AND g4.ID = g.ID + 2 ) AS Dept

    ,Polldate

   

 FROM #GroupMemberShipTemp g

 )

INSERT GroupMembership

SELECT

    ID

       ,GroupName

       ,LTRIM(RTRIM(UserName)) AS UserName

       ,LTRIM(RTRIM(FullName)) AS FullName

       ,LTRIM(RTRIM(Dept)) AS Dept

       ,Polldate

FROM groupCTE

WHERE UserName IS NOT NULL

ORDER BY GroupName

       ,UserName

<end>