Hi... Please run this under Host/SQL and let me know if this fixes you up.
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DataSprings_DynamicLogin_SyncUserRolesBetweenPortals') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}DataSprings_DynamicLogin_SyncUserRolesBetweenPortals
GO
create procedure {databaseOwner}[{objectQualifier}DataSprings_DynamicLogin_SyncUserRolesBetweenPortals]
(@MasterPortalID int,@ChildPortalID int,@UserID int)
as
begin
-- generate ##temp tables
-- drop table ##tempMasterPortalRoles if exists
IF object_id('tempdb..##tempMasterPortalRoles') is not null
BEGIN
DROP TABLE ##tempMasterPortalRoles
END
IF object_id('tempdb..##tempMatchedChildMasterRoles') is not null
BEGIN
DROP TABLE ##tempMatchedChildMasterRoles
END
IF object_id('tempdb..##tempUserMasterRoleInfo') is not null
BEGIN
DROP TABLE ##tempUserMasterRoleInfo
END
IF object_id('tempdb..##tempUserChildRoleInfo') is not null
BEGIN
DROP TABLE ##tempUserChildRoleInfo
END
--List all Roles in the Master Portal
select r.RoleID, r.RoleName into ##tempMasterPortalRoles from {objectQualifier}roles r
where r.PortalID = @MasterPortalID and r.RoleName != 'Administrators'
--Get a list of all Matching Roles in the Child Portal (We only want to modify these roles for the user)
select r.RoleID as ChildRoleID, mpr.roleID as MasterRoleID, r.RoleName into ##tempMatchedChildMasterRoles from {objectQualifier}roles r
inner join ##tempMasterPortalRoles mpr on r.RoleName = mpr.rolename
where r.PortalID = @ChildPortalID and r.RoleName != 'Administrators'
-- Lets get the matched role info for this user for the masterportal
select UserRoleID, UserID,RoleID,ExpiryDate,IsTrialUsed,EffectiveDate,CreatedByUserID,CreatedOnDate,LastModifiedByUserID,LastModifiedOnDate, mpr.MasterRoleID, mpr.ChildRoleID
into ##tempUserMasterRoleInfo from {objectQualifier}UserRoles ur
inner join ##tempMatchedChildMasterRoles mpr ON ur.RoleID = mpr.masterroleID
where UserId=@UserID
-- Lets get the matched role info for this user for the chid Portal
select UserRoleID, UserID,RoleID,ExpiryDate,IsTrialUsed,EffectiveDate,CreatedByUserID,CreatedOnDate,LastModifiedByUserID,LastModifiedOnDate, cpr.MasterRoleID, cpr.ChildRoleID
into ##tempUserChildRoleInfo from {objectQualifier}UserRoles ur
inner join ##tempMatchedChildMasterRoles cpr ON ur.RoleID = cpr.childroleID
where UserId=@UserID
--Lets Delete any roles that have been removed from the user in the Master Portal
Delete from {objectQualifier}UserRoles Where UserRoleID In
(
Select UserRoleID
from ##tempUserChildRoleInfo where MasterRoleID not in (Select RoleID from ##tempUserMasterRoleInfo)
)
--Update any Expiry dates for roles
Update {objectQualifier}UserRoles
SET ExpiryDate = info.ExpiryDate,
IsTrialUsed = info.IsTrialUsed,
EffectiveDate = info.EffectiveDate,
CreatedByUserID = info.CreatedByUserID,
CreatedOnDate = info.CreatedOnDate,
LastModifiedByUserID = info.LastModifiedByUserID,
LastModifiedOnDate = info.LastModifiedOnDate
FROM {objectQualifier}UserRoles ur INNER JOIN ##tempUserMasterRoleInfo info ON ur.UserID = @UserID and ur.RoleID = info.ChildRoleID
-- Finally, lets add any new roles in now
insert into {objectQualifier}UserRoles(UserID,RoleID,ExpiryDate,IsTrialUsed,EffectiveDate,CreatedByUserID,CreatedOnDate,LastModifiedByUserID,LastModifiedOnDate)
select info.UserID,info.childRoleID,info.ExpiryDate,info.IsTrialUsed,info.EffectiveDate,info.CreatedByUserID,
info.CreatedOnDate,info.LastModifiedByUserID,info.LastModifiedOnDate
from ##tempUserMasterRoleInfo info
where info.childRoleID not in (select RoleID from ##tempUserChildRoleInfo)
end
|