/* new permission, current permission */ grantNewPermissionToGroupsWithCurrentPermission( 'DistributorAcctMgmtChange', 'DistributorManagementEdit', "MFR"); grantNewPermissionToGroupsWithCurrentPermission( 'DistributorProfilePurchasing', 'SalesDataVisibility', "MFR,BRO"); grantNewPermissionToGroupsWithCurrentPermission( 'DistributorAcctClassChange', 'DistributorManagementEdit', "BRO"); grantNewPermissionToGroupsWithCurrentPermission( 'DistributorAcctSalesRepChange', 'DistributorManagementEdit', "BRO"); grantNewPermissionToGroupsWithCurrentPermission( 'DistributorAcctPriorityChange', 'DistributorManagementEdit', "BRO"); grantNewPermissionToGroupsWithCurrentPermission( 'AllOperators', 'OperatorManagement', "MFR,BRO"); grantNewPermissionToGroupsWithCurrentPermission( 'OperatorProfilePurchasing', 'OperatorManagement', "MFR,BRO"); declare @currentPermission varchar(50) = ; declare @newPermission varchar(50) = ; --find clients that have a secure area for the current permission, that do not have a secure area for the new permission with missingSecureAreas as ( select distinct a.ownerId, a.fsl_tablecode from tbl_SEC_Attributes a -- have the current permission inner join tbl_SEC_AttributeTypes t on t.attrbtypeId = a.attrbTypeId where t.attrbTypeCode = @currentPermission and a.fsl_tablecode in ( ) and ( or ( a.fsl_tablecode = and a.ownerId = ) ) and a.ownerId > 0 -- do not have the new permission and not exists ( select * from tbl_SEC_Attributes aa inner join tbl_SEC_AttributeTypes tt on tt.attrbTypeId = aa.attrbTypeId where tt.attrbTypeCode = @newPermission and aa.ownerId = a.ownerId and aa.fsl_tablecode = a.fsl_tablecode ) ) -- insert the new secure areas for the clients that were missing one insert into tbl_SEC_Attributes ( ownerId, fsl_tablecode, seqNum, attrbName, attrbDescription, attrbTypeId ) select msa.ownerId, msa.fsl_tablecode, 99 seqNum, t.attrbTypeName as attrbName, t.attrbTypeDesc as attrbDescription, t.attrbTypeId from missingSecureAreas msa inner join tbl_SEC_AttributeTypes t on t.attrbTypeCode = @newPermission; -- any group linked to the current permission, is to be lined to the new permission if it is not already so insert into tbl_SEC_AttrbGrpLink ( groupId, attrbId ) select distinct cagl.groupId, na.attrbId from tbl_SEC_AttrbGrpLink cagl -- current secure areas inner join tbl_SEC_Attributes ca on ca.attrbId = cagl.attrbId inner join tbl_SEC_AttributeTypes cat on cat.attrbTypeId = ca.attrbTypeId and cat.attrbTypeCode = @currentPermission -- the client's secure areas for the new permission inner join tbl_SEC_Attributes na on na.ownerId = ca.ownerId and na.fsl_tablecode = ca.fsl_tablecode inner join tbl_SEC_AttributeTypes nat on nat.attrbTypeId = na.attrbTypeId and nat.attrbTypeCode = @newPermission where ca.fsl_tablecode in ( ) and ( or ( ca.fsl_tablecode = and ca.ownerId = ) ) and ca.ownerId > 0 -- client group is not aleady linked to client's secure area for new permission and not exists ( select * from tbl_SEC_AttrbGrpLink nagl where nagl.groupId = cagl.groupId and nagl.attrbId = na.attrbId ); declare @currentPermission varchar(50) = ; declare @newPermission varchar(50) = ; select cg.fsl_tablecode, cg.ownerId, cg.groupId currentGroupId, cg.groupName currentGroupName, cat.attrbTypeCode currentPermission, nat.attrbTypeCode newPermission, ng.groupId, ng.groupName from tbl_SEC_AttrbGrpLink cagl -- current secure areas inner join tbl_SEC_Attributes ca on ca.attrbId = cagl.attrbId inner join tbl_SEC_AttributeTypes cat on cat.attrbTypeId = ca.attrbTypeId and cat.attrbTypeCode = @currentPermission inner join tbl_SEC_Groups cg on cg.ownerId = ca.ownerId and cg.fsl_tablecode = ca.fsl_tablecode and cg.groupId = cagl.groupId -- the client's secure areas for the new permission inner join tbl_SEC_Attributes na on na.ownerId = ca.ownerId and na.fsl_tablecode = ca.fsl_tablecode inner join tbl_SEC_AttributeTypes nat on nat.attrbTypeId = na.attrbTypeId and nat.attrbTypeCode = @newPermission inner join tbl_SEC_AttrbGrpLink nagl on nagl.attrbId = na.attrbId inner join tbl_SEC_Groups ng on ng.ownerId = na.ownerId and ng.fsl_tablecode = na.fsl_tablecode and ng.groupId = nagl.groupId where ca.fsl_tablecode in ( 'MFR', 'BRO' ) and ( or ( ca.fsl_tablecode = and ca.ownerId = ) ) and ca.ownerId > 0 order by cg.fsl_tablecode, cg.ownerId, cg.groupName