Creative Commons License
Drekendrop | Blog of Tutorial by Mei Pakpahan is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Based on a work at drekendrop.blogspot.com.
Permissions beyond the scope of this license may be available at http://softdadesign.co.nr.

Friday, March 11, 2011

SQL Query: Retrieve Active Directory Users List

Today i was struggling getting the active directory users list from our domain, but finally it worked. Here's the query.

1st step---


EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'bisdragons.com'
GO

Note: Change @datasrc=N'your_domain.com' 

2nd step--- 
Run this query:
SELECT samAccountName, givenName [First Name], sn [Last Name],mail FROM OPENQUERY(ADSI,'SELECT samAccountName,givenName, sn, mail FROM ''LDAP://OU=Teaching Faculty,OU=Staff,DC=BISDRAGONS,DC=COM'' WHERE objectClass=''Person'' AND objectClass = ''User'' ORDER BY samAccountName') WHERE mail IS NOT NULL

Note: change "LDAP://OU=your_organizational_unit, DC=your_domain,DC=COM(*optional com,org,ca,etc...)" 

You can also modify the SQL query above to get another attributes from your Active Directory. This link provides a complete Active Directory attributes in pdf.

Good luck!


5 comments:

Michiel said...

Please not that querying AD is limited to 3000 records (1000 standard) so putting your 'where' outside the 'openquery' could lead to misleading results. Better query would be:

SELECT samAccountName, givenName [First Name], sn [Last Name],mail FROM OPENQUERY(ADSI,'SELECT samAccountName,givenName, sn, mail FROM ''LDAP://OU=Teaching Faculty,OU=Staff,DC=BISDRAGONS,DC=COM'' WHERE objectClass=''Person'' AND objectClass = ''User'' AND mail>0 ORDER BY samAccountName')

Mei Pakpahan said...

Thanks for the suggestions Michiel!

Anonymous said...

If I wanted to further narrow this down to only users in a specific group membership how would I accomplish that. For example I'm looking for something like this:

SELECT samAccountName, givenName [First Name], sn [Last Name],mail
FROM OPENROWSET('ADSDSOObject','adsdatasource','SELECT samAccountName,givenName, sn, mail
FROM ''LDAP://COPPOLA.LOCAL/dc=COPPOLA,dc=LOCAL''
WHERE objectClass=''Person'' AND objectClass = ''User''
AND mail>0
AND Group=''Sales*''
ORDER BY samAccountName')

Thank in advance for your help,
sks

Abdul Waheed said...

hi, is it possible to get multi value properties form AD like description, memberOf. if I run simply by adding memberOf this gives error

select * FROM OPENQUERY(ADSI,'SELECT initials, samAccountName, displayName, distinguishedName, mail, memberOf FROM ''LDAP://DC=corp, DC=contoso, DC=com'' WHERE objectClass=''Person''')
"ERROR"
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.
Is it possible to get data form multi values. I am using SQL Server 2008 R2

Anonymous said...

no, it is not. it looks like openquery command does not support this, because you can do it in code, C# or VB.NET for example. so, if you really need this functionality, you can create CLR proc, which will do it.

Post a Comment

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Grants For Single Moms