Active Directory Security Groups
-
MSSQL2000
I am trying to find a way to retrieve a list of Security Groups and the associated users.
This query will return all the groups successfully:
SELECT*
FROM
OPENROWSET('ADSDSOObject', 'adsdatasource;', 'SELECT adspath, groupType
FROM ''LDAP://corp.company.com'' where objectCategory=''group''')
ORDER BY GroupName
This query will return the users successfully:
SELECT*
FROM
OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT cn, mail, displayName, name, samaccountname
FROM ''LDAP://corp.company.com'' where objectClass = ''Person''')
I just cannot find the link between the two! Thanks in advance!
-
I keep finding examples in vbscript but I do not use that. If this is the only way, is there an example of a script that returns ALL users and their groups association?
Thanks again!
-
I'll take a look. Most of my work in this area has been using ADSI (VBScript or Perl). Is there a reason you have to do this through SQL Server?
K. Brian Kelley
@kbriankelley -
Thank you! The only reason is due to lack of experience in VBScript. If I found a solution, I would not be opposed to it. At this point, just getting something to work is in my best interest.
I appreciate it!!
-
Hi
Did you find a solution to this one? I really want to do this in SQL rather than vbscript.
-
Been there and done that - but haven't got the code on me! I'll look it up tonight after work and see if I can't post a solution for you as I spent a good 3 months last year messing with AD queries via SQL.
Steve
-
Right.
My memory is a little hazy as it was a while ago that I did this and stupidly, when I left the company last year (in a hurry because I hated them with a passion and I had a better job to go to!) I rather foolishly didn't take a copy of all my work on this, which should be a lesson to us all - keep regular backups of your work somewhere you can always get at it (although obviously I would never advocate keeping information that might breach your terms of employment, simply the theoretical and practical workings without passwords and suchlike).
So, you know how to get your groups and as I understand it, you wish to enumerate the users within the groups. Well, get your groups into a table variable using an insert-select statement. Now loop through the groups and build a dynamic sql query to retrieve the users in each group. Now, I know that some people will gasp in horror at the idea of dynamic sql but there is no way around it as the OPENQUERY function will not accept variables in it's arguments and this is why the variables and the temporary table column are all NVARCHAR - sp_executesql demands NVARCHAR. It's unlikely you'll need more than 4000 characters, but if you do then you can use VARCHAR and just the EXEC command but that really is frowned upon. Note the excessive number of single quotes: If they aren't right, you're boned. Believe me, it took me a while to get the right numbers in place. The LDAP address is simple - <your domain controller machine name>.<your company domain name>.<suffix>. the OPENQUERY call assumes you have a linked server set up for ADSI, called, in this case, ADSI. If you haven't already got this set up, the following snippet will do it:
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
There are several excellent websites covering ADSI (google ADSI or Active Directory Services Interface - again, I left the links behind. D'oh!) and O'Reilly do a couple of excellent books which I highly recommend trackign down in paper or electronic form if you intend to do any amount of ADSI programming: Active Directory and The Active Directory Cookbook. Granted, most of the samples are in VBScript, but there are plenty of resources on t'interwebnet which will show you the SQL syntax for what you need, although it does take some digging.
Note that there are three common ways to bind to and search AD objects:
WinNT provider (WinNT://)
Global Cache (GC://)
Lightweight Directory Access Protocol (LDAP://)
Don't use the GC because there are issues with it not always being up to date as despite it's name it does not mirror every part of an AD forest and if you need to work accross multiple domains, as I have done in the past, there is no guarantee the information will be there. LDAP is the best to use as it is well documented and widely supported. Learning to script ADSI is well worth the effort because you'll be surprised how useful it is and how good a thing it is for your CV (as is learning to script IIS commands and controls which can then be executed by SQL - it's a very simple yet powerful way of controlling web sites during essentail database tasks although thats by-the-by).
I have purposely left the example script below simple in terms of actually doing anything with the output - I'll leave that up to you as a challenge. I won't guarantee it'll work first time either, partly because I do not happen to have an AD domain handy to test against and partly because as I said at the start, it's been a while. ADSIVB Script programming does occasionaLearninglly require you to take a 'suck it and see' approach and don't be afraid to experiment: so long as you're only reading data from AD you can't do any harm.
One final thing: If you have got the Microsoft ADSIEedit managment management console snap in on your PC (Start > Run > adsiedit should do it if you have) and you have enough privilege to use it to browse your AD domain, this will help you massively as it has a handy query builder and will help you navigate its rather byzantine structure.
Best of luck and I hope this helps!
Steve
Steve Pettifer
Head of Development
TMTI Ltd
DECLARE @Query NVARCHAR(3872)
DECLARE @Mygroup NVARCHAR(128)
DECLARE @Groups TABLE (GroupName NVARCHAR(128))
INSERT INTO @Groups
SELECT --etc - your group retrieval code here.
WHILE (SELECT COUNT(*) FROM @Groups) > 0
BEGIN
SELECT TOP 1 @Mygroup = GroupName FROM @Groups
SELECT @Query = 'SELECT co, company, department, givenName, sn, l, mail, sAMAccountName, telephoneNumber, title
FROM OpenQuery(ADSI, ''SELECT co, company, department, givenName, sn, l, mail, sAMAccountName, telephoneNumber, title
FROM ''''LDAP://mydc.mycorp.net/CN=' + @Mygroup + ',DC=mycorp,DC=net'''' where objectClass = ''''User'''' and userAccountControl<>514)'
EXEC sp_executesql @Query
DELETE FROM @Groups WHERE GroupName = @Mygroup
END
-
I forgot to mention that the userAccountControl <> 514 condition filters out all users flagged as inactive but not users who have just been disabled, for example. If you want only current, active users, change the condition to userAccountControl = 512. You can find the full list of these values on the web too, google userAccountControl. I'm sorry I don't have these links to hand any more but it's not terribly hard to find.
If you already knew some/most of what I've put above, I do apologise, but of course it's hard to tell what knowledge level there is so I find it best to assume knowledge is basic or less and therefore if you give a full, clear explanation then hopefully everyones a winner.
-
Thank you, Steve! I am putting some efforts towards testing, but I wanted to immediately thank you for taking the time to write this up.
-
No worries. Glad I can help out and give something back to the community for a change!
Steve
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply