After being pointed in the right direction (thanks Chad!), I believe I have a "fix" for the problem described above. The [DataSprings_RetrieveDNNUserProfileData3] stored procedure needs to be modified slightly...here is the complete SQL for my new version:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[DataSprings_RetrieveDNNUserProfileData3]
@PortalID int,
@RoleFilter nvarchar(1000),
@LimitResults nvarchar(1000),
@Authonly int
AS
Declare curDS Cursor
FOR
SELECT PropertyName, PropertyDefinitionID
FROM dbo.[ProfilePropertyDefinition]
where Visible=1
AND PortalID = @PortalID
AND Deleted=0
AND PRopertyName <> 'FirstName'
AND PropertyName <> 'LastName'
AND PRopertyNAme <> 'E-Mail'
AND PRopertyNAme <> 'Email'
AND PRopertyNAme <> 'Username'
AND PropertyNAme Not Like '%.%'
AND PropertyNAme Not Like '%-%'
--AND PropertyNAme Not Like '%/%'
AND PropertyNAme Not Like '%#%'
ORDER BY PropertyCategory, Vieworder
FOR READ ONLY;
Declare @PropertyName VarChar(500);
Declare @PropertyDefinitionID Integer;
Declare @SQLToUse VarChar(8000);
Declare @SQLToUseJoin VarChar(8000);
Declare @SQLToExecute nVarChar(Max);
Declare @Alias VarChar(10);
set @PropertyName = '';
set @SQLToUse = '';
set @SQLToUseJoin = '';
OPEN curDS;
FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID;
WHILE @@FETCH_STATUS = 0
BEGIN
if DataLength(@SQLToUse) = 0
BEGIN
set @SQLToUse = 'select ' + @LimitResults + ' U.UserID,U.Username,FirstName, LastName, U.Email, '
set @SQLToUseJoin = 'dbo.DSGetProfileElementByID(U.userID,''' + Convert(varchar(4), @PropertyDefinitionID) + ''') as [' + REPLACE(@PropertyName, '/', '') + ']'
END
ELSE
BEGIN
set @SQLToUseJoin = @SQLToUseJoin + ',dbo.DSGetProfileElementByID(U.userID,''' + Convert(varchar(4), @PropertyDefinitionID) + ''') as [' + REPLACE(@PropertyName, '/', '') + ']'
END;
FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID;
END
CLOSE CurDS;
DEALLOCATE CurDS;
set @SQLToUseJoin = @SQLToUseJoin + ' from dbo.[Users] U
INNER JOIN dbo.[UserPortals] AS up ON up.UserId = U.UserId
Inner Join dbo.[aspnet_users] as au on au.Username = U.UserName
Inner Join dbo.[aspnet_membership] am on au.UserID = am.UserID
WHERE up.IsDeleted = 0 AND (am.IsApproved =1 OR ' + Convert(varchar(2), @AuthOnly) + ' = 0)
AND U.UserID In (Select UserID from dbo.[UserPortals] where PortalID = ' + Convert(varchar(2), @PortalID) + ')'
Set @SQLToExecute = 'Select * from (' + Convert(varchar(8000), @SQLToUse) + Convert(varchar(8000), @SQLToUseJoin) + ') AS A ' + Convert(varchar(8000), @RoleFilter)
--print @SQLToExecute
--EXEC(@SQLToExecute);
--print @SQLToExecute;
exec sp_executesql @SQLToExecute
For those playing along at home, the change involves adding another INNER JOIN (to reference the UserPortals table) and the corresponding WHERE clause to the final SQL statement to be executed.
Hopefully this fix is all it takes, and will help someone else!
Thanks,
Chris