USP_MKTGIFTRECORDSOURCE_GRANTRIGHTSTOREVENUETABLES

CREATE procedure dbo.[USP_MKTGIFTRECORDSOURCE_GRANTRIGHTSTOREVENUETABLES]
(
  @ID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max);

  begin try
    --If it is the BBEC record source, then don't grant rights because it should already have all the necessary rights...

    if dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@ID) = 0
      begin
        --Grant rights to the MKTREVENUELETTER and MKTREVENUERECEIPT tables...

        set @SQL = 'grant select, insert, update, delete on dbo.[' + dbo.[UFN_MKTREVENUELETTER_MAKETABLENAME](@ID) + '] to BBAPPFXSERVICEROLE';
        exec (@SQL);

        set @SQL = 'grant select, insert, update, delete on dbo.[' + dbo.[UFN_MKTREVENUERECEIPT_MAKETABLENAME](@ID) + '] to BBAPPFXSERVICEROLE';
        exec (@SQL);
      end
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;