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
Related Questions
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?
Ad