March 18, 2009 at 4:21 am
Hi,
I need to implement a solution in my work company, they want to know the group members from diferent groups listed in active directory. So I presented a solution that was building a SSIS package that extract the info from active directory, list it to a txt file, and then put all the data in the SQL Server tables. My problem here is that i'm not a developer, and my problem here is to get a code that build me the txt file with the data I need. the fields are, "samaccountname", "memberof", "displayname", "departament" and "group".
any one can help me with this?
thks
March 18, 2009 at 4:28 am
You will need to write custom script to access the active directory, this site has something similar to what you are doing..
March 18, 2009 at 5:14 am
yes, i already implemented that, but I having problems editing the code to add this costum fields, as i said I not a developer, and the code development is very hard to me.
March 20, 2009 at 9:23 am
Hi
you can create a linked server to your domain controller.
then
declare @groups table ( grp sysname )
declare @usrs table ( usr sysname, grp sysname )
declare @actgrp sysname
declare @actntgrp sysname
declare @max-2 int
declare @i int
insert @groups SELECT *
FROM OPENQUERY( [<linked server name>],
'SELECT Name
FROM ''LDAP://<linked server name>/ DC=<subdomain>,DC=<domain>,DC=<net>''
WHERE
objectClass = ''group''
ORDER BY name
')
select @max-2 = count(*) from @groups
select @i = 0
select @actgrp = Min(grp) from @groups
while @i < @max-2 begin
select @i = @i + 1
select @actntgrp = '<domain>\' + @actgrp
insert @usrs select name, @actntgrp from OpenRowset(NetGroupGetMembers, @actntgrp)
select @actgrp = MIN(grp) from @groups where grp > @actgrp
end
will give you logins and groups.
if exists (select * from tempdb.dbo.sysobjects where name = N'##ADS' )
drop table [dbo].[##ADS]
SELECT *
INTO ##ADS
FROM OPENQUERY( <linked server name>,
'SELECT sAMAccountName, userPrincipalName, company, department, Name, givenName, SN, Mail, telephoneNumber, mobile,
l, physicalDeliveryOfficeName, postalCode, streetAddress, facsimileTelephoneNumber, msExchHideFromAddressLists, distinguishedName, info, title, st, userAccountControl
FROM ''<linked server name>/ DC=<subdomain>,DC=<domain>,DC=<net>''
WHERE sAMAccountType=805306368 AND NOT ''userAccountControl:1.2.840.113556.1.4.803:''=2
ORDER BY SN
')
will give you a lot of information about the users.
joining the results should give you everything you need
SELECT *
FROM ##ADS JOIN @usrs
ON @usrs.usr = ##ADS.sAMAccountName
regards
karl
Best regards
karl
June 25, 2013 at 8:38 am
New code for SQL Server 2005 and above:
declare @groups table ( id int identity, grp sysname, distinguishedName VARCHAR(MAX) )
declare @usrs table ( id int identity, usr sysname, grp sysname )
declare @actgrp sysname
declare @actntgrp sysname
declare @max-2 int
declare @i int
, @distinguishedName VARCHAR(MAX)
, @sql VARCHAR(MAX)
insert @groups (grp, distinguishedName)
SELECT Name, distinguishedName
FROM OPENROWSET('ADSDSOObject', 'adsdatasource';'<domain\user>';'<password>',
'SELECT Name, distinguishedName
FROM ''LDAP://<ads server name>/ DC=<subdomain>,DC=<domain>,DC=<net>''
WHERE
objectClass = ''group''
ORDER BY name
')
--SELECT * FROM @groups order by id
select @max-2 = MAX(id) from @groups
select @i = 0
while @i < @max-2 begin
select @i = @i + 1
select @actgrp = '<domain>' + grp, @distinguishedName = distinguishedName from @groups
WHERE id = @i
SELECT @sql = 'select sAMAccountName, ''' + @actgrp + ''' from OPENROWSET(''ADSDSOObject'', ''adsdatasource'';''<domain\user>'';''<password>'',
''SELECT givenName, SN, Mail, info, sAMAccountName
FROM ''''LDAP://<ads server name>/DC=<subdomain>,DC=<domain>,DC=<net>''''
where objectCategory = ''''Person'''' and objectClass = ''''User'''' and SN = ''''*'''' and sAMAccountType=805306368 and not ''''userAccountControl:1.2.840.113556.1.4.803:''''=2 AND memberOf = ''''' + @distinguishedName + ''''' '')'
insert @usrs (usr, grp) EXEC(@sql)
end
select * from @usrs
Best regards
karl
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy