spCloneSite_ObjectSecurity

CREATE PROCEDURE [dbo].[spCloneSite_ObjectSecurity]
(
@SourceClientSitesID int,
@TargetClientSitesID int,
@CopyBatchGUID uniqueidentifier
)
AS

begin try
    begin transaction
        declare @CopiedObjects table (oldGUID uniqueidentifier, newGUID uniqueidentifier)

        -- get guids for copied images
        insert into @CopiedObjects(oldGUID, newGUID)
        select old.Guid,
               new.Guid
        from dbo.SiteImages old
        inner join dbo.SiteImages new on old.ID = new.CopySourceID
        where new.CopyBatchGUID = @CopyBatchGUID

        -- get guids for copied stylesheets
        insert into @CopiedObjects(oldGUID, newGUID)
        select old.Guid,
               new.Guid
        from dbo.SiteStyleSheets old
        inner join dbo.SiteStyleSheets new on old.ID = new.CopySourceID
        where new.CopyBatchGUID = @CopyBatchGUID

        -- get guids for copied pages
        insert into @CopiedObjects(oldGUID, newGUID)
        select old.Guid,
               new.Guid
        from dbo.SitePages old
        inner join dbo.SitePages new on old.ID = new.CopySourceID
        where new.CopyBatchGUID = @CopyBatchGUID

        -- get guids for copied email templates
        insert into @CopiedObjects(oldGUID, newGUID)
        select old.Guid,
               new.Guid
        from dbo.EmailTemplate old
        inner join dbo.EmailTemplate new on old.ID = new.CopySourceID
        where new.CopyBatchGUID = @CopyBatchGUID

        -- get guids for copied parts
        insert into @CopiedObjects(oldGUID, newGUID)
        select old.Guid,
               new.Guid
        from dbo.SiteContent old
        inner join dbo.SiteContent new on old.ID = new.CopySourceID
        where new.CopyBatchGUID = @CopyBatchGUID

        -- get guids for copied documents
        insert into @CopiedObjects(oldGUID, newGUID)
        select old.Guid,
               new.Guid
        from dbo.DocUploadPart old
        inner join dbo.SiteContent SC on SC.CopySourceID = old.SiteContentID
        inner join dbo.DocUploadPart new on new.SiteContentID = SC.ID
        where SC.CopyBatchGUID = @CopyBatchGuid

        -- get guids for copied discussion groups
        insert into @CopiedObjects(oldGUID, newGUID)
        select old.Guid,
               new.Guid
        from dbo.DiscussionGroups old
        inner join dbo.SiteContent SC on SC.CopySourceID = old.SiteContentID
        inner join dbo.DiscussionGroups new on new.SiteContentID = SC.ID
        where SC.CopyBatchGUID = @CopyBatchGuid

        -- get guids for copied weblogs (and other news channels)
        insert into @CopiedObjects(oldGUID, newGUID)
        select old.Guid,
               new.Guid
        from dbo.NewsChannels old
        inner join dbo.SiteContent SC on SC.CopySourceID = old.SiteContentID
        inner join dbo.NewsChannels new on new.SiteContentID = SC.ID
        where SC.CopyBatchGUID = @CopyBatchGuid

        -- add guids for copied event calendar categories
        insert into @CopiedObjects(oldGUID, newGUID)
        select old.CalendarCategoryPicksID,
               new.CalendarCategoryPicksID
        from dbo.CalendarEvtCategoryPicks old
        inner join EventCalendar EC on EC.ID = old.EventCalendarID
        inner join SiteContent SC on SC.CopySourceID = EC.SiteContentID and SC.CopyBatchGUID = @CopyBatchGUID
        inner join EventCalendar EC2 on EC2.SiteContentID = SC.ID
        inner join CalendarEvtCategoryPicks new on new.EventCalendarID = EC2.ID
        where old.CategoryCodeTableEntryID =  new.CategoryCodeTableEntryID

        -- add guids for the old/new site object itself
        declare @sourceSiteGuid uniqueidentifier
        declare @targetSiteGuid uniqueidentifier
        select @sourceSiteGuid = Guid from dbo.ClientSites where ID = @SourceClientSitesID
        select @targetSiteGuid = Guid from dbo.ClientSites where ID = @TargetClientSitesID

        insert into @CopiedObjects(oldGUID, newGUID)
        values (@sourceSiteGuid, @targetSiteGuid)

        -- clone everyone security privs for copied objects
        declare @CURRENTDATE datetime = getdate();
        declare @CHANGEAGENTID uniqueidentifier;
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

        insert into CMSEVERYONESECURITYPRIVS(SECUREDOBJECTGUID, SECUREDOBJECTTYPEID, OBJECTTASKID, VALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select CO.newGUID,
               ESP.SECUREDOBJECTTYPEID,
               ESP.OBJECTTASKID,
               ESP.VALUE,
               @CHANGEAGENTID,
               @CHANGEAGENTID,
               @CURRENTDATE,
               @CURRENTDATE
        from dbo.CMSEVERYONESECURITYPRIVS ESP
        inner join @CopiedObjects CO on ESP.SECUREDOBJECTGUID = CO.oldGUID

        -- clone security exceptions for copied objects
        insert into dbo.CMSOBJETSECURITYEXCEPTION(SECUREDOBJECTGUID, EXCEPTIONOBJECTGUID, OBJECTTASKID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select CO.newGUID,
               OSE.EXCEPTIONOBJECTGUID,
               OSE.OBJECTTASKID,
               @CHANGEAGENTID,
               @CHANGEAGENTID,
               @CURRENTDATE,
               @CURRENTDATE
        from dbo.CMSOBJETSECURITYEXCEPTION OSE
        inner join @CopiedObjects CO on OSE.SECUREDOBJECTGUID = CO.oldGUID

        commit transaction
    end try
begin catch
    rollback transaction
    UPDATE SiteCopy SET Status = 8, ErrorLog = ErrorLog +  'Error Copying Security:' + ERROR_MESSAGE() + CHAR(13) WHERE ID = @CopyBatchGUID
end catch