Query Active Directory Group Membership

Question

Post reply

Query Active Directory Group Membership

checkai

Hall of Fame

Points: 3041
More actions
March 26, 2010 at 11:26 am

#213201

Is there a way (I've already done a ton of searching) to have an active directory user name and query to see if they are members of a certain AD group with SQL?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Use the ADSI provider:

http://www.lmgtfy.com/?q=sql+server+ADSI+%22linked+server%22

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

That search should provide several examples on the topic.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

zdonev Valued Member Points: 69 More actions · Answer 3

Hi Everyone,

Is there also a way how to a user if he is an indirect member of a group.

Lets look at following scenario:

User ONE is a member of GROUP A

User TWO is a member of GROUP B

GROUP A is a member of GROUP B

GROUP B

|___User TWO

|___GROUP A

|___User ONE

User a should have right to group B since GROUP A is a member of GROUP B. But when I query GROUP B through SQL it gives me only the direct users that are members of this group. How can I change my query to give me all users that have rights to GROUP B?

Here is my query:

SELECT @remotesql = 'SELECT name, adsPath, mail, sAMAccountName

FROM ' + dbo.udf_Quotestring('LDAP://DC=mydomain,DC=inc') + '

WHERE objectCategory =' + dbo.udf_Quotestring('Person') + '

AND objectClass=' + dbo.udf_Quotestring('user') + '

AND sAMAccountName=' + dbo.udf_Quotestring(@user) + '

AND memberOf=' + dbo.udf_Quotestring('CN='+ @group + ',CN=Users,DC=mydomain,DC=inc');

SELECT @localsql = 'SELECT * FROM OPENQUERY(ADSI, ' + dbo.udf_Quotestring(@remotesql) + ')';

EXEC (@localsql)

NOTE: the dbo.udf_Quotestring is just a function to help me with the quote formating!

Thanks for the help!