Sql Server: how to write a trigger on a table with SELECT negation

Question

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

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

This gives me an error, although when I say "SELECT PERMISSIONS DENIED", I guess because of SELECT FROM INSERTED.

How can I save SELECT undo, but allow a SELECT trigger with an INSERTED pseudo?

Thanks in advance!

Answer 1

Consider adding an EXECUTE AS clause so that the trigger fires with the permissions of the schema owner.

CREATE TRIGGER [dbo].[TR_Product_Update] ON [Product]
   WITH EXECUTE AS OWNER
   AFTER UPDATE
AS
SELECT ProductId
FROM INSERTED

Answer 2

? , ? . , - , , , .

- EDIT -

, SQL Server . 2005, , .

Session Variable - Context_Info: Session - . SQL-Server , . 128 .

Answer 3

, - :

update t1
set updated = getdate()
from table1 t1
join inserted i
on i.rowid = t1.rowid

, , , .

Answer 4

, , UPDATE SELECT.

:

DROP DATABASE triggerPermissionTest
CREATE DATABASE triggerPermissionTest
GO
USE triggerPermissionTest
GO
CREATE USER foo FROM LOGIN tester
GO
CREATE TABLE triggerTable (id int)
GO
DENY SELECT ON triggerTable to foo
GRANT UPDATE ON triggerTable to foo
GO
CREATE TRIGGER execAsTrigger ON triggerTable
AFTER UPDATE AS SELECT * FROM triggerTable
GO
INSERT INTO triggerTable VALUES (1)
GO


"":

UPDATE triggerTable SET id = 2
GO
UPDATE triggerTable SET id = id *2
GO


, ( select * from triggerTable , , ).

, triggerTable ( triggerTable, id ).

, , UPDATE .