How to import user info from active directory in tsql

Transact-SQL
1

Need to pull Name Samaccount and department from active directory from TSQL any hlep would be great.

2

I believe there's an ODBC or similar driver that can query Active Directory, but I haven't seen anything written on it in over 10 years. I think it's layered on LDAP, and the performance is not good.

An easier way is to use either PowerShell or the dsquery/dsget command line utilities to query AD.

dsquery user -name "*"

I don't think you can pipe dsquery output to dsget, but you can use a for construct:

for /F %a in ('dsquery user -name "*" ') do dsget user %a -samid >>all_users.txt

You might have to clean up that data to be able to import into SQL Server. There are Active Directory PowerShell cmdlets that can most likely give you cleaner output, I'm just not familiar with them. dsget can probably get the department as well, I don't happen to know the syntax but you can run dsget -? to get a list.

Once you get the data output to a CSV or other text file you can import it using bcp or BULK INSERT.

Edit: As of Windows 2008/2012 dsquery/dsget are not included with the OS, you have to add the Active Directory feature in order to install them. Same for the Active Directory PowerShell cmdlets. You can add them via Server Manager, or via PowerShell Import-Module (have to look up which module though)

3

What is the use case? there is a reason that data stays in Active Directory.

Read this

  FROM OPENQUERY( ADSI,   
                  'SELECT Name, mail, displayName,givenname,sn,cn,distinguishedName, SAMAccountName, objectGUID, Department, adspath, manager, whencreated, ManagedBy  
                 FROM ''LDAP://yourcompany.lan/DC=yourcompany,DC=lan''   
       where objectClass = ''User''   
         AND objectCategory = ''Person''   
      AND ''userAccountControl:1.2.840.113556.1.4.803:'' <>2  ')