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 |