Querying Active Directory Group membership from SQL

Querying Active Directory Group membership from SQL

Inspired from (& based on) code @ http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

This code looks at an LDAP v2 source eg; Windows 2000 and fetches users, groups and membership information.

You need to set up a linked server called 'ADSI' to get this to function.

Setting up the linked server -

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\user',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'

The code itself -

-- Configure AD Domain to search

DECLARE  @strDC           NVARCHAR(100)
SET @strDC = 'dc=domain,dc=co,dc=uk'

-- Declare Variables

DECLARE  @chvAlphaChars   VARCHAR(60),
         @chvSearch       VARCHAR(10),
         @chvSearchLevel1 VARCHAR(1),
         @chvSearchLevel2 VARCHAR(1),
         @chvSearchLevel3 VARCHAR(1),
         @intcountLevel1  INT,
         @intcountLevel2  INT,
         @intcountLevel3  INT,
         @intRowCount     INT,
         @strSQL          NVARCHAR(4000),
         @strADSISQL      NVARCHAR(4000),
   @Login     VARBINARY(85),
   @CN     VARCHAR(512),
   @CT     INT,
   @ADRoot    VARCHAR(255)

                        
-- Declare temp tables

IF EXISTS (SELECT *
     FROM   TEMPDB.DBO.SYSOBJECTS
     WHERE  ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Users'))
  DROP TABLE #LDAP_AD_V2_USERS
  
CREATE TABLE [DBO].[#LDAP_AD_V2_USERS] (
  [ROW_ID]          [INT]   IDENTITY ( 1,1 )   NOT NULL,
  [SID]             [VARBINARY](85)   NULL,
  [SAMACCOUNTNAME]  [NVARCHAR](256)   NULL,
  [CN]              [NVARCHAR](256)   NULL,
  [SN]              [NVARCHAR](256)   NULL,
  [DISPLAYNAME]     [NVARCHAR](256)   NULL,
  [GIVENNAME]       [NVARCHAR](256)   NULL,
  [TELEPHONENUMBER] [NVARCHAR](256)   NULL,
  [ADSPATH]         [NVARCHAR](256)   NULL,
  [HOMEDIRECTORY]   [NVARCHAR](256)   NULL,
  [MAIL]            [NVARCHAR](256)   NULL,
  [MEMBEROF]        [NVARCHAR](256)   NULL,
  [PRIMARYGROUPID]  [INT],
  [CREATETIMESTAMP] DATETIME)
ON [PRIMARY]

IF EXISTS (SELECT *
           FROM   TEMPDB.DBO.SYSOBJECTS
           WHERE  ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Groups'))
     DROP TABLE #LDAP_AD_V2_GROUPS
  
CREATE TABLE [DBO].[#LDAP_AD_V2_GROUPS] (
  [ID]          [INT]   IDENTITY ( 1,1 )   NOT NULL,
  [LOGIN]       [VARCHAR](512)   NULL,
  [EMAIL]       [VARCHAR](255)   NULL,
  [ACCTNAME]    [VARCHAR](512)   NULL,
  [DISTNAME]    [VARCHAR](512)   NULL,
  [CREATEDDATE] [DATETIME]   NULL,
  [CHANGEDDATE] [DATETIME]   NULL,
  [MGR]         [VARCHAR](512)   NULL,
  [SID]         [VARBINARY](85)  NULL)
ON [PRIMARY]

IF EXISTS (SELECT *
           FROM   TEMPDB.DBO.SYSOBJECTS
           WHERE  ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_UserGroups'))
     DROP TABLE #LDAP_AD_V2_USERGROUPS
  
CREATE TABLE [DBO].[#LDAP_AD_V2_USERGROUPS] (
  [USERLOGIN]  [VARBINARY](85)   NOT NULL,
  [GROUPLOGIN] [VARBINARY](85)   NULL)
ON [PRIMARY]

-- POPULATION OF USERS

-- Search letters to cycle through             
-- any chars, but the first char must be a space
SET @chvAlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ.-_`0123456789'
SET NOCOUNT ON

-- start on non space char
SET @intcountLevel1 = 2
-- first level loop
WHILE @intcountLevel1 <= LEN(@chvAlphaChars)
  BEGIN
    -- get first level char
    SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)                           
    -- reset start on space
    SET @intcountLevel2 = 1                          
    -- second level loop
    WHILE @intcountLevel2 <= LEN(@chvAlphaChars)
      BEGIN
        -- reset start on space
        SET @intcountLevel3 = 1
        -- third level loop
        WHILE @intcountLevel3 <= LEN(@chvAlphaChars)
          BEGIN
            -- setup the string to search for. By using the trim function we can form each level depending on no records
            -- eg A 99, B 1000 > BA 9, BB 20 etc
            -- trim the spaces forming just A, B, C ; AA, AB for search etc
            SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
            SET @chvSearchLevel2 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel2,1))
            SET @chvSearchLevel3 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel3,1))
   SET @chvSearch = @chvSearchLevel1 + @chvSearchLevel2 + @chvSearchLevel3
            SET @strADSISQL = 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath,  homedirectory, mail, primarygroupid, createTimeStamp ' 
       + CHAR(13) + 'from  ''''LDAP://' + @strDC + '''''  ' 
       + CHAR(13) + 'where objectCategory = ''''Person''''  ' 
       + CHAR(13) + 'and   objectClass = ''''user'''' ' 
       + CHAR(13) + 'and   sAMAccountName = ''''' + @chvSearch + '*'''' '                                                                                                                                                                                                                                                                                                                                                                                                      
            SET @strSQL = 'insert into #LDAP_AD_V2_Users (sid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath,  homedirectory, mail, primarygroupid, createTimeStamp) ' 
       + CHAR(13) + 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath,  homedirectory, mail, primarygroupid, createTimeStamp ' 
       + CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) ' 
       + CHAR(13) + 'order by sAMAccountName'
            
            EXEC SP_EXECUTESQL @strSQL
              
            SET @intRowCount = @@ROWCOUNT
                               
            -- prints what string is being searched for : no of inserts
            -- PRINT @chvSearch + ' : ' + CONVERT(VARCHAR,@intRowCount)
            
            -- if searched on @chvSearchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B
            IF @intRowCount < 1000
               AND @chvSearchLevel2 = ''
              SET @intcountLevel2 = @intcountLevel2 + 100
                                                      
            -- if searched on @chvSearchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB
            IF @intRowCount < 1000
               AND @chvSearchLevel3 = ''
              SET @intcountLevel3 = @intcountLevel3 + 100
                                                      
            -- else over 1000 so increment third level
            SET @intcountLevel3 = @intcountLevel3 + 1
          END
          
        -- increment next second level char
        SET @intcountLevel2 = @intcountLevel2 + 1
      END
      
    -- increment next first level char
    SET @intcountLevel1 = @intcountLevel1 + 1
  END
  

-- POPULATION OF GROUPS

SET @strADSISQL = 'select objectSid, managedBy, whenChanged, whenCreated, distinguishedName, name, samAccountName, mail ' 
       + CHAR(13) + 'FROM ''''LDAP://' + @strDC + '''''  ' 
       + CHAR(13) + 'WHERE objectCategory = ''''Group'''' '
                                                                                                                                                                                                                                                                                                                           
SET @strSQL  = 'insert into #LDAP_AD_V2_Groups (sid,mgr,changeddate,createddate,distname,acctname,email,login) ' 
      + CHAR(13) + 'select objectSid,managedBy,whenChanged,whenCreated,distinguishedName,name,mail,samAccountName ' 
      + CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) ' 
      + CHAR(13) + 'order by sAMAccountName'

-- PRINT @strSQL
EXEC SP_EXECUTESQL  @strSQL
  

-- POPULATION OF USER > GROUPS RELATIONSHIP TABLE
                 
IF EXISTS (SELECT *
           FROM   TEMPDB.DBO.SYSOBJECTS
           WHERE  ID = OBJECT_ID('tempdb.dbo.#CT'))
  DROP TABLE #CT
  
-- Create a temporary table to hold AD user SID values
CREATE TABLE #CT (CT VARBINARY(85))

-- Declare and open a cursor to step through the list of Active Directory groups
DECLARE CURGROUPS CURSOR  FOR
SELECT   SID,
         DISTNAME
FROM     #LDAP_AD_V2_GROUPS
ORDER BY LOGIN

OPEN CURGROUPS

FETCH NEXT FROM CURGROUPS
INTO @Login,
     @CN

WHILE @@FETCH_STATUS = 0
  BEGIN
    -- Empty the temp table
    TRUNCATE TABLE #CT
    
    -- Build a SQL statement to insert the SID values directly from the linked server into the temp table
    SET @strsql = 'INSERT #CT
    SELECT *
    FROM OPENQUERY (
    ADSI,
    ''SELECT objectSid
    FROM ''''LDAP://' + @strDC + '''''  
    WHERE objectCategory = ''''User''''
    AND memberof=''''' + REPLACE(@CN,'''','''''''''') + ''''''')'
    
    EXEC( @strsql)
    
    -- Select the number of records inserted. If this value is less than 1000 then there is no need
    -- to execute the OLE calls, and we simply copy the values into the correlation table.
    SELECT @CT = COUNT(* )
    FROM   #CT

    --PRINT @ct    
    --PRINT @strsql
    
    IF @CT <> 0
      BEGIN
        INSERT #LDAP_AD_V2_USERGROUPS
              (USERLOGIN,
               GROUPLOGIN)
        SELECT CT,
               @Login
        FROM   #CT
        WHERE  CT IS NOT NULL
      END
      
    FETCH NEXT FROM CURGROUPS
    INTO @Login,
         @CN
  END
  
CURSORERROR:
CLOSE CURGROUPS
DEALLOCATE CURGROUPS
DROP TABLE #CT

/*
-- Individual SQL Statements to see users, groups & relationships
select * from #LDAP_AD_V2_Users
select * from #LDAP_AD_V2_Groups
select * from #LDAP_AD_V2_UserGroups
*/

-- Final Query to tie together the 3 tables and produce a report of group membership

SELECT #LDAP_AD_V2_GROUPS.ACCTNAME AS GROUPNAME,
       #LDAP_AD_V2_USERS.SAMACCOUNTNAME AS USERACCOUNT
FROM     #LDAP_AD_V2_USERGROUPS
         INNER JOIN #LDAP_AD_V2_USERS
           ON #LDAP_AD_V2_USERGROUPS.USERLOGIN = #LDAP_AD_V2_USERS.SID
         INNER JOIN #LDAP_AD_V2_GROUPS
           ON #LDAP_AD_V2_USERGROUPS.GROUPLOGIN = #LDAP_AD_V2_GROUPS.SID
ORDER BY #LDAP_AD_V2_GROUPS.ACCTNAME,
         #LDAP_AD_V2_USERS.SAMACCOUNTNAME