We created a sp to sync users across all portals that runs every half hour. Now no matter what portal the user hits they get authenticated.
USE [EFTPortal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SyncUserPortalsAndRoles]
AS
-- Variables for subqueries
DECLARE @PortalID int, @PortalName nvarchar(128), @NewUsers int, @PortalUsers int, @RegisteredUserRoleID int
-- Portal Cursor
DECLARE cur_portal CURSOR FOR
SELECT p.PortalID, p.PortalName, r.RoleID
FROM Portals p LEFT JOIN Roles r ON (r.PortalID = p.PortalID)
WHERE r.RoleName = 'Registered Users'
ORDER BY PortalID desc
-- Table Variable for Insert Records
DECLARE @tbl_UserID TABLE ( [UserID] int )
-- Open the Cursor
OPEN cur_portal
-- Retrieve the first record
FETCH NEXT FROM cur_portal
INTO @PortalID, @PortalName, @RegisteredUserRoleID
-- Loop while fetch retrieves records
WHILE @@FETCH_STATUS = 0
BEGIN
-- Report Start Of Work
PRINT N'Beginning work for Portal: ' + CAST(@PortalID AS nvarchar) + ' - ' + @PortalName
PRINT N'Registered User Role: ' + CAST(@RegisteredUserRoleID AS nvarchar)
-- Delete Users that have been removed from the main portal
DELETE FROM UserPortals
WHERE UserID in ( SELECT UserID
FROM Users
WHERE UserID NOT IN ( SELECT UserID
FROM UserPortals
WHERE PortalID = 0 )
AND UserName NOT IN ('host','admin')
)
-- Retrieve the new users
PRINT N'Retrieve New User Table'
INSERT INTO @tbl_UserID ( [UserID] )
SELECT DISTINCT [UserID]
FROM UserPortals
WHERE [UserID] NOT IN ( SELECT DISTINCT [UserID]
FROM UserPortals
WHERE [PortalID] = @PortalID )
-- Retrieve the Insert User Count
PRINT N'Retrieve New User Count'
SELECT @NewUsers = COUNT(DISTINCT [UserID])
FROM @tbl_UserID
PRINT N'Count: ' + CAST(@NewUsers AS nvarchar)
-- Retrieve the Current User Count
PRINT N'Retrieve Current User Count'
SELECT @PortalUsers = COUNT(DISTINCT [UserID])
FROM UserPortals
WHERE [PortalID] = @PortalID
PRINT N'Count: ' + CAST(@PortalUsers AS nvarchar)
-- Begin the Transaction
BEGIN TRY
BEGIN TRANSACTION;
PRINT N'Insert New Users into UserRoles'
-- Insert the missing records into User Roles
INSERT INTO UserRoles ( [UserID], [RoleID] )
SELECT DISTINCT [UserID], @RegisteredUserRoleID AS [RoleID]
FROM @tbl_UserID
WHERE [UserID] NOT IN ( SELECT [UserID]
FROM UserRoles
WHERE [RoleID] = @RegisteredUserRoleID )
-- Insert the missing records into User Portals
PRINT N'Insert New Users into UserPortals'
INSERT INTO UserPortals ( [UserID], [PortalID], [CreatedDate], [Authorised] )
SELECT DISTINCT [UserID], @PortalID AS [PortalID], GetDate() AS [CreatedDate], 1 AS [Authorised]
FROM @tbl_UserID
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT N'Rolling Back Transaction'
ROLLBACK TRANSACTION;
END CATCH
-- Clear New Users
PRINT N'Deleting UserID Records'
DELETE FROM @tbl_UserID
-- Fetch next record
FETCH NEXT FROM cur_portal
INTO @PortalID, @PortalName, @RegisteredUserRoleID
-- Line Break
PRINT N'----------------------------------------------'
END
-- Cleanup cursor
CLOSE cur_portal
DEALLOCATE cur_portal