spCloneRolePrivs

CREATE PROCEDURE [dbo].[spCloneRolePrivs] (
        @SrcRoleID  int,
    @TargetRoleId int
)
AS

declare @CURRENTDATE datetime = getdate();
declare @CHANGEAGENTID uniqueidentifier;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @SRCGUID uniqueidentifier
set @SRCGUID = (select Guid from dbo.ClientRoles where ID=@SrcRoleID)
declare @TGTGUID uniqueidentifier
set @TGTGUID = (select Guid from dbo.ClientRoles where ID=@TargetRoleId)

INSERT INTO CMSOBJETSECURITYEXCEPTION ( 
    SECUREDOBJECTGUID,
    EXCEPTIONOBJECTGUID,
    OBJECTTASKID,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED
)SELECT               
    SECUREDOBJECTGUID,
    @TGTGUID,
    OBJECTTASKID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE
FROM        
    CMSOBJETSECURITYEXCEPTION
WHERE       
    EXCEPTIONOBJECTGUID = @SRCGUID

update EventCalendar set ManagerRoles = ManagerRoles + CONVERT(nvarchar(10),@TargetRoleId) + ';'
where ManagerRoles like '%;' + CONVERT(nvarchar(10),@SrcRoleId) + ';%'
 or ManagerRoles like CONVERT(nvarchar(10),@SrcRoleId) + ';%'