LDAP query to get a user memberships in SQL

 

LDAP query to get a user memberships in SQL

Today I was struggling to obtain in SQL Server 2005 the list of groups (from Active Directory) to which a user belongs to. Here is the final result. Enjoy it!

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

/* get the groups for the user Test1*/
select * from openquery
(
ADSI,'SELECT name 
FROM ''LDAP://abc.com''
WHERE objectCategory = ''Group'' AND objectClass = ''group''
AND member=''CN=Test1,CN=Users,DC=abc,DC=com''
')

Alina's technical blog : LDAP query to get a user memberships in SQL