Hi there,
i've been trying to apply a ddl trigger that just logs all DDL DB events to a central DB.table. It works fine if the table and trigger are in the same DB.
So I have DB1.dbo.Storage where I would like store all the events.
I apply the same DDL Trigger to DB2; whenthe trigger tries to fire, it doesn't want to run because it looks like it's running in the security context of the user in DB2; this user does not have access to db1.dbo.storage.
Is there a way to force the trigger to run without having to add additional security permissions on all the DBs?
Or do I need to add all the users to the db1.dbo.storage table to allow this trigger to work?
I tried added a WITH EXECUTE AS 'SA' hoping it would run SA which had access to all DBs.
Cheers