It is not recommended to do anything in the database, but as there is no way to do this on the UI, you have no other choice. Before you start: Backup your database to be able to roll back if something unexpected happens!

The error you get is because one (or more) of these users is already in the main portal, so you get a violation of the key UserId + PortalID. The user with UserId = 157 is obviously already registered in the portal with the PortalId 0.

What you have to do update only those users that don't have a record with the PortalId of the main portal, and then delete all those records that have a different PortalId. Also make sure that the users are assigned to the "Registered Users" role in the main portal (see table Roles, each role is assigned to a portal, so the role "Registered Users" exists in every portal with a different ID!).

After that, restart the application pool (important!), and check if everything is as expected.

Happy DNNing!
Michael

 

Thanks for the reply Michael.

What exactly did you mean with this statement

"What you have to do update only those users that don't have a record with the PortalId of the main portal"   

What record exactly did you mean?

And also Which tables needs to edited?

Do you have a backup of the site with the portals still intact?

If so, you can use the fordnn /usersexportimport module (https://github.com/fordnn/usersexportimport) to export users from the portals you've deleted and import them into the portal you are saving.

Posted By markjoeylavapie on 27 Apr 2021 03:42 PM

What exactly did you mean with this statement

"What you have to do update only those users that don't have a record with the PortalId of the main portal"   

What record exactly did you mean?

And also Which tables needs to edited?

The table UserPortals has two fields for the primary key: UserId and PortalId - the combination of these two fields has to be unique in the table.

If you have a user with a UserId = 150, and this user is assigned to two portals, say PortalId = 0 (main portal) and PortalId = 1, you can't change the PortalId from 1 to 0 in the second case, as this would violate the primary key constraint. Therefore you have to delete the record with UserId = 150 and PortalId = 1, so the user is only assigned to the main portal.

If the user only has 1 record in the UserPortals table, and the PortalId is different than 0 you can change that value to 0.

If you have more than one records for this UserId, but none of them has PortalId = 0, then you should change one of these records to PortalId = 0 and delete the other one(s).

Hope you got the idea.

And: Backup you database before you start any of these changes, to be able to rollback if something goes wrong.

And: Restart the application pool after these changes.

Happy DNNing!
Michael

 

Hi Micheal,

Thank you for the detailed instruction. I did exactly what you said, but the system keeps generating new UserPortal record for the record that I deleted. 

Example. If  I delete UserID=4 PortalID=1 UserPortalID=123. Once the user attempted another login, the user will still be redirected to Portal 1. And a new record on the database will be generated, and this time... UserID=4 PortalID=1 UserPortalID=124

please be aware the userportal record is not sufficient - the user also needs roles assigned and profile property values generated. Although it is possible (but requires some additional effort) to do this using SQL, it would be preferred to use DNN API methods to create the user for the new site.

It is most propably what Sebastian said, I mentioned this in my first post in this thread.

Also don't forget to restart the application pool after the changes, otherwise data is coming from the cache and does not reflect your changes.

Happy DNNing!
Michael