Gerard's codebase: Importing Active Directory Details to SQL Server

Gerard's codebase

This is a personal repository of code snippets and information.

Over the years I have generated lots of little sub programs and stored away acres of useful code snippets. The problem is always to locate them.

Even more time wasting is forgetting how to do simple things when you havnt developed in an environment for a few years (or even a few months)

My new years resolution is to start putting them up in a common place as I produce them. (thanks google)

They are handy for me and, with a bit of a clean up and documentation, they might be handy for others if they wander in here.

Gerard 2008

Friday, October 3, 2008

Importing Active Directory Details to SQL Server

The following script can be used to import Active Directory data into a Sql table.

Note - there is a big limitation with using this - only the first 1000 entries will be selected

You need to initially create the Active Directory database as a linked server.

The script uses subquerys to allow for manipulation in the SELECT Field1,Field2,Field3,Field4 FROM statement and you will have to put in your own AD specific details for the parameters and fields.

Apologies for the code formating problems I had with this - remove the underscores

-- to link AD Server
-- sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
BEGIN TRANSACTION
  TRUNCATE TABLE AdUserDetails
  INSERT INTO AdUserDetails (Field1, Field2, Field3, Field4)
        -- Select the required fields from Active Directory
        SELECT  Field1,Field2,Field3,Field4 FROM
            (SELECT TOP 100 PERCENT *
            FROM OPENQUERY(ADSI,
            '<LDAP:/ /dc=YourADParam1,dc=YourADParam2,dc=YourADParam3>;(&(objectCategory=person)(objectClass=user)(name=*));AdField1,AdField2,AdField3,AdField4;subtree') AS t
            WHERE AdField3 IS NOT NULL) AS ad
COMMIT TRANSACTION
select * from AdUserDetails

Gerard's codebase