Ad

Sql Server: How To Write Trigger On A Table With Denied SELECT

Hello I have a table on which I have denied SELECT privs to a user.
This table has a trigger which references the INSERTED table, basically doing an

AFTER UPDATE SET <table>.[UPDATED] = getdate() 
  WHERE ROWID IN SELECT ROWID FROM INSERTED

It is giving me an error though, saying "SELECT PERMISSIONS DENIED", I am guessing because of the SELECT FROM INSERTED.

How can I keep the SELECT deny, but allow the trigger to SELECT from the INSERTED pseudotable?

Thanks in advance!

Ad

Answer

Consider adding an EXECUTE AS clause so the trigger runs with the schema owner's permissions.

CREATE TRIGGER [dbo].[TR_Product_Update] ON [Product]
   WITH EXECUTE AS OWNER
   AFTER UPDATE
AS
SELECT ProductId
FROM INSERTED
Ad
source: stackoverflow.com
Ad