What sql GP user principle do I need to GRANT the CREATE privilage to for my trigger to still work?

Question

I put a trigger on [dbo].[POP10300] in my company database to add a record in a custom table when ever a PO is received:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[POReceivingTrigger] 
   ON  [dbo].[POP10300] 
   AFTER INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
INSERT INTO [cp].[PrintQueue] ([Created], [Module], [Trigger], [Alert or Report], [Event])
    SELECT
        GETDATE()
       ,'GP'
       ,POPRCTNM
       ,'BackOrderedItemsReceived'
       ,'PO Received'
    FROM INSERTED
END

It works for the GP user sa but not for any other GP user. They receive this error popup when receiving:

What sql GP user principle do I need to GRANT the INSERT privilege to on [cp].[PrintQueue] for the trigger to still work?

Answer 1

What sql GP user principle do I need to GRANT the INSERT privilege to on [cp].[PrintQueue] for the trigger to still work?

By default, triggers execute as the user running the DML statement that caused the trigger to fire. So either

1. change the execution context of the trigger with EXECUTE AS OWNER,

2. grant all the users INSERT on the target table, or

3. ensure that the target schema and the trigger's object's schema are owned by the same user to enable Ownership Chains to suppress permissions checking on the INSERT.