0

I have 2 Databases. Database A belongs to an ERP System and Database B is my own database.

I've created an AFTER INSERT DML Trigger in a table which belongs to Database A. Every single ERP User is associated with a DB User. At the moment I copy all the users from Database A to Database B to grant permission for my tables.

It was the only solution I could find to make it work but it's bad because a new ERP User will get an Error Message when there's no user in Database B.

So now I tried it once again to use WITH EXECUTE AS .... I tried OWNER and different DB users but nothing works and I have absolutely no idea what's wrong. I even granted all permissions for the user on the tables I use in the trigger.

Let's say I have a user called "triggerUser" when I do WITH EXECUTE AS 'triggerUser' it doesn't work but when I remove the EXECUTE AS .... and use the triggerUser for the ERP Login it works so the user got the permissions to write my tables.

Here's my trigger. Maybe you have an idea what I'm missing.

CREATE TRIGGER [dbo].[TR_manAddAutomatikQueueForLager_AfterInsert]                          
ON [SL_MWAWI].[dbo].[LAGERPROTOKOLL]
WITH EXECUTE AS 'moep'
AFTER INSERT 
AS
BEGIN
SET NOCOUNT ON

DECLARE @Mandant int
SET @Mandant = (SELECT MANDANT_ID FROM SL_Daten.dbo.MANDANT WHERE Datenbankname = 'SL_MWAWI')

-- insert
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
    INSERT INTO maniacSellerGen2.dbo.manAutomatikQueue 
    SELECT @Mandant, ISNULL(wsa.WebShopId, wsav.WebShopId), ISNULL(wsav.VaterArtikelnummer, wsa.Artikelnummer), 'Lager', GETDATE() 
    FROM inserted
    LEFT JOIN maniacSellerGen2.dbo.manWebShopArtikel wsa 
    ON wsa.Artikelnummer COLLATE DATABASE_DEFAULT = inserted.Artikelnummer COLLATE DATABASE_DEFAULT 
    AND @Mandant = wsa.Mandant
    LEFT JOIN maniacSellerGen2.dbo.manWebShopArtikelVarianten wsav
    ON wsav.Artikelnummer COLLATE DATABASE_DEFAULT = inserted.Artikelnummer COLLATE DATABASE_DEFAULT
    AND @Mandant = wsav.Mandant
    WHERE ((wsa.Artikelnummer IS NULL AND wsav.Artikelnummer IS NOT NULL)
    OR (wsa.Artikelnummer IS NOT NULL AND wsav.Artikelnummer IS NULL))
END
END

Here's a screenshot from the permissions for the user in every database it has db_datareader and db_datawriter.

Permissions

I also did tried this:

GRANT INSERT ON dbo.manAutomatikQueue TO moep
GRANT DELETE ON dbo.manAutomatikQueue TO moep
GRANT UPDATE ON dbo.manAutomatikQueue TO moep
GRANT SELECT ON dbo.manAutomatikQueue TO moep

Thanks a lot!

Simon Klopmann
  • 1
  • 1
  • 1
    Perhaps [this suggestion](https://stackoverflow.com/questions/66200311/execute-a-trigger-always-with-sa-user) for a similar problem might work for you - a bit more complicated using a certificate. – SMor Feb 16 '21 at 02:09
  • Use the Certificate option as proposed by @SMor. Do not compromise your database security. – Martin Cairney Feb 16 '21 at 06:46
  • @SMor Thank you, i will take a look at the certificates. – Simon Klopmann Feb 16 '21 at 12:47
  • @MartinCairney is it really that bad in general to use EXECUTE AS? I mean in my example the user got a lot more permission but if there's a special user for a trigger with only the permissions and access to the databases used by the trigger? I just wanted to show that even with all permissions it doesn't work for me with EXECUTE AS but when I know what's wrong I would create a new user with only the permissions needed for the trigger. I will have a look at certificates and give it a try but I still would like to know what's wrong with the EXECUTE AS to get a better understanding. – Simon Klopmann Feb 16 '21 at 12:47
  • ```EXECUTE AS``` is not inherently bad. My call out was that if you are going cross database, then Certs is the correct approach. Otherwise you need to consider tings like the Trustworthy setting for the database which "MAY" open other avenues to inappropriate permissions. Always tread carefully with security and set least privilege – Martin Cairney Feb 18 '21 at 01:58

0 Answers0