Here goes the scenario:
Need to pass an active directory security group and get the memberOf.
Tried table value function does not allow EXEC to execute and return a table containing the results simply because I am using openquery with LDAP.
So, calling an sp within function is out of question.
The results can be achieved by a stored procedure but the "caller" must be able to get the results in a table form.
I am open for suggestions, but at this time, I am planning on finding out if there is a way to convert an SP to a CLR.
Ideas are welcome.
Mean while, there is my code in SP that works.
ALTER PROCEDURE [dbo].[USP_DisplayMemberOf_ADGroup]
@ADGroup nvarchar(64) = '__MyGroup__'
SET NOCOUNT ON;
declare @PartOne nvarchar(1000), @PartTwo nvarchar(2000)
set @PartOne = 'SELECT cn, company, department, displayName, employeeID, facsimileTelephoneNumber, givenName
, groupType, lastLogoff, mail, manager, mobile, ou, physicalDeliveryOfficeName, sAMAccountName
, sAMAccountType ,sn, telephoneNumber, title, url, userAccountControl, userPrincipalName
WHERE memberOf = ''cn='+ @ADGroup + ',OU=Groups,OU=ParentGroup,DC=CompanyNameOrDomain,DC=ad'''
set @PartTwo =N'
FROM OPENQUERY (ADSI, ''' + replace(@PartOne, '''', '''''') + ''' )'
The only way that the MemberOf in openquery is with double string and that is not possible in a table returned function.
appreciate your help.
Please let me know if there is any other additional information that's lacking in here. .