Larry Daniele
going with the flow Posts:57
|
01-29-2010 08:17 AM |
|
It seems that the User Directory module does not heed the IsDeleted field in the Users table.
I have created a Host (Super User) account and seen it in the directory. I then deleted this account, but the record still appears in the directory.
In looking at the Users table, I was surprised to find an entry for this deleted user. However, I also did see that the IsDeleted field for this user is set to True. So perhaps this needs to be taken into account in a future DUD release. |
|
|
|
|
Candace
river guide Posts:2431
|
01-29-2010 08:48 AM |
|
Hi Larry,
This is an issue that popped up with DNN 5.x discussed here as the 'soft delete issue.'
http://support.dotnetnuke.com/issue...p;PROJID=2
http://www.dotnetnuke.com/Community/Forums/tabid/795/forumid/107/threadid/345274/scope/posts/Default.aspx
As a workaround, can you set your directory to display authorized users only in Module Configuration? Let me know how that works for you. Thanks!
Candace |
|
|
|
|
David To
river guide Posts:2719
|
01-29-2010 09:04 AM |
|
HI, DUD does take into consideration on "IsDeleted". If you look at your installation file for "02.50.60.SqlDataProvider" or use SQL management studio to check the stored procedure "DataSprings_RetrieveDNNUserProfileData3" there is a section of code: -- check if DNN version is 5.0 or higher if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) + ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = ' + cast(@PortalID as varchar(3)) + ')' EXECUTE(@MySQL) END else -- if DNN version is lower than 5.0 BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) + '' EXECUTE(@MySQL) END NOTE: The "Users" table column "IsDeleted" is not the correct one to use. You need to check out the "UserPortals" table for the UserID and IsDeleted column. If this Isdeleted column is set to 1 then it should not show in the DUD directory. Please check out this table to verify. I know I tested this out and it was working fine on my localhost system with multiple portals. When I deleted a user on a portal and did a search on in DUD logged into that portal, that user no longer showed up. -- David |
|
|
|
|
Larry Daniele
going with the flow Posts:57
|
01-29-2010 11:54 AM |
|
@Candace: If I configure the module to "Include" only "Registered Users", then yes it displays just the valid users for the current portal. Thanks for the work-around!
If I uncheck "Registered Users", then the deleted Host (Super User) account is displayed BUT the valid Host account is NOT displayed. Interesting - a clue perhaps?!?
@David: The UserPortals table does not contain either of these Host users (but they are both in the Users table). So I think there still might be a problem with the SQL. Please contact me directly if you would like more details. |
|
|
|
|
David To
river guide Posts:2719
|
02-01-2010 07:29 AM |
|
HI, can you try it on a regular user? I don't think HOST users will work since by default, a HOST user will have access to all the portals and therefore, if you delete a HOST user, it will not show in the "userportals" table. -- David |
|
|
|
|
Larry Daniele
going with the flow Posts:57
|
02-01-2010 10:46 AM |
|
Yes, Dynamic Directory correctly handles deleted portal users. But the issue still stands that deleted host users appear in the Directory when they shouldn't. |
|
|
|
|
Candace
river guide Posts:2431
|
02-01-2010 01:14 PM |
|
Hi Larry,
Thanks for the details. We may have to review this further for a future release. Can you make a go of it right now with the workaround -- include only Registered Users? Thanks!
Candace |
|
|
|
|
Larry Daniele
going with the flow Posts:57
|
02-01-2010 02:00 PM |
|
Yes, the work-around is good for now. Onward! |
|
|
|
|
David To
river guide Posts:2719
|
02-02-2010 01:19 PM |
|
HI Larry, here's the modified SQL stored procedure. Just to to host / sql and copy and execute the following SQL. Then go to host/host settings and click on "Restart Application". This should fix the Host users showing in DUD. I've test it on my local system and it works fine. Thanks for pointing this out to us since I wasn't taking into consideration host users which is set in "users" table and not "userportals" table. -- David ALTER Procedure {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3] (@PortalID int,@RoleFilter nvarchar(1000),@LimitResults nvarchar(100),@Authonly int,@Filter nvarchar(4000)) AS BEGIN if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') and (@PortalID = 999) begin drop table {databaseOwner}tempDUDProfile end if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999) begin drop table {databaseOwner}tempDUDPivot end -- generate tempDUDProfile table -- create table tempDUDProfile if not exists If not exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') BEGIN select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,UP.propertyvalue, P.propertyname, P.PortalID into {databaseOwner}tempDUDProfile from {databaseOwner}[{objectQualifier}Users] U inner join {databaseOwner}[{objectQualifier}userprofile] UP on U.userid = UP.userid inner join {databaseOwner}[{objectQualifier}profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from {databaseOwner}[{objectQualifier}UserPortals] where PortalID = Convert(varchar(3), @PortalID)) where P.Visible=1 --AND P.PortalID = @PortalID AND P.Deleted=0 AND P.PRopertyName <> 'FirstName' AND P.PropertyName <> 'LastName' AND P.PRopertyNAme <> 'E-Mail' AND P.PRopertyNAme <> 'Email' AND P.PRopertyNAme <> 'Username' AND P.PRopertyNAme <> 'Displayname' AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!' exec {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3a] END If (@PortalID <> 999) BEGIN Declare @MySQL nvarchar(max) -- check if DNN version is 5.0 or higher if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') and exists (select top 1 * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%users' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) + ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}users] where issuperuser = 1 and isdeleted = 1) AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = ' + cast(@PortalID as varchar(3)) + ')' EXECUTE(@MySQL) END else if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') and not exists (select top 1 * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%users' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) + ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = ' + cast(@PortalID as varchar(3)) + ')' EXECUTE(@MySQL) END else -- if DNN version is lower than 5.0 BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) +'' EXECUTE(@MySQL) END --PRINT(@MySQL) END END |
|
|
|
|
Larry Daniele
going with the flow Posts:57
|
02-02-2010 02:00 PM |
|
Thanks David. I ran this SQL (with "Execute as Script" checked) and I can confirm that it solves the problem I was seeing. Also, it took effect without having to restart the application. I appreciate the quick response! |
|
|
|
|