How can I verify the groups a user belongs to using an SQL server linked to Active Directory?

I have an SQL Server 2005 Server linked to a Windows 2003 Active Directory using LDAP. I have a query similar to the following (which works)  to verify which users are in the Active Directory. How can I modify that query to get me the all of the Active Directory groups to which a user belongs?

Thanks in advance


select  *  
 from  openquery(adsi, '  
 select  givenName,  
     sn,  
     sAMAccountName,  
     displayName,  
     mail,  
     telephoneNumber,  
     mobile,  
     physicalDeliveryOfficeName,  
     department,  
     division  
 from    ''LDAP://someserver/DC=mydc,DC=mysubdc,DC=net''  
 where   objectCategory = ''Person''  
         and  
         objectClass = ''user''  
	
 ')

Open in new window

CompmindAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gothamiteCommented:
I don't think you can do this because the AD attribute memberOf (which is the one that contains the DNs of a user's group memberships) is a multi-valued attribute, and you cannot retrieve multi-valued attributes with this sort of interface.

I had the same problem and ended up coding a CLR stored procedure to enumerate the query and return the data as comma-separated.

Here is a very useful article which will enable you to do the same thing. I know it looks long but it works great:

http://blogs.msdn.com/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompmindAuthor Commented:
gothamite, that does seem to work but it seems like it will take me a while to code if no one else answers with something a bit simpler I'll give it a shot.

Regards,
Gabriel
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.