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