Query Active Directory Groups and the Users in each Group

Question

Query Active Directory Groups and the Users in each Group

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
June 22, 2010 at 3:12 pm

#236809

To stay compliant with some of the standards today, we need to report on the database logins. Most of our logins are Active Directory login. How can I query LDAP to get all the Groups that are listed in SQL, and select all the users from within each group?
Does anyone have a query that can perform this?
Thank you in advance
Andrew SQLDBA

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Andrew i have this saved in my snippets; it's creating a linked server to Active directory, and querying for some stuff;

when i tried it on my own network, i didn't have permission, but you might have more rights on the network than my login did:

l--doesn't quite work...permissions

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

select * from openquery

(ADSI,'SELECT cn, mail, co, distinguishedName, displayName

FROM ''LDAP://disney''

where objectClass = ''disney\lowell'' '

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

You can also use the below code, we use it to figure out group membership without having to engage domain admins.

EXEC xp_logininfo 'whateverdomain\whatevergroup', 'members'

Andrew

Lowell SSC Guru Points: 323518 More actions · Answer 3

andrewkane17 (6/22/2010)
You can also use the below code, we use it to figure out group membership without having to engage domain admins.
EXEC xp_logininfo 'whateverdomain\whatevergroup', 'members'
Andrew

omg that extended proc with no parameters is awesome; lists all my groups; that is very very handy and going into my snippets;

thanks andrew kane!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!