Interesting question and solution guys.
There is another thread related to this one: http://www.dotnetnuke.com/Community/Forums/tabid/795/forumid/-1/threadid/143210/scope/posts/Default.aspx. You find the trigger (in the middle of the thread) to add UserID to table UserPortals table. The trigger is tied to table Users and it handles both Insert and Update events. However, it does not include code to assign users to Registered Users role like Ian has provided. Combine Ian's code and the trigger in other thread as below:
-- =============================================
-- Author: combine code from Fuji and Ian
-- Description: Trigger to add userid, portalid to UserPortals table
-- and assign user to Registered Users role.
--WARINGING - This trigger affects all portals
-- =============================================
CREATE TRIGGER {databaseOwner}{objectQualifier}trig_User_Multiportal
ON Users
AFTER INSERT, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @UserID int
-- Insert statements for trigger here
--Get new userid from INSERTED table
SELECT @UserID = UserID FROM INSERTED
--add records to UserPortals table
INSERT INTO {databaseOwner}{objectQualifier}UserPortals
(
UserId,
PortalId
)
SELECT
@UserId AS UserID,
PortalId
FROM {databaseOwner}{objectQualifier}Portals
WHERE PortalID NOT IN (
SELECT PortalID
FROM {databaseOwner}{objectQualifier}UserPortals WHERE UserID = @UserID
)
--assign to Registered Users role
INSERT INTO UserRoles
(
UserId,
RoleId
)
Select @UserId AS UserID, RoleId
FROM {databaseOwner}{objectQualifier}Roles
WHERE RoleName = 'Registered Users'
AND RoleID NOT IN (
SELECT RoleID
FROM {databaseOwner}{objectQualifier}UserRoles
WHERE UserID = @UserID)
END
Notice the NOT IN clause was used to insure no duplicate records are added to table UserPortals and UserRoles. To apply the trigger, login as Host account and go to Host -> SQL menu to execute the script.
If you want to remove the triger, run the script below:
DROP TRIGGER {databaseOwner}{objectQualifier}trig_User_Multiportal
Have lots of fun with DNN.