Menu
Just a quick question:
Say I put an insert trigger on a table in my database.
If data is inserted into that table through a login/user "foobar".
Does the trigger execute with the same access rights / permissions as "foobar"?
Many thanks.
440
In my experience, they are not a good idea because they can result in surprising side effects, and are difficult to debug (especially when one trigger fires another). Often developers do not even think of looking if there is a trigger.
Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.
Row-level trigger is identified by the FOR EACH ROW clause in the CREATE TRIGGER command. Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into the Customer table, then a statement-level trigger on that table would only be executed once.
To invoke a trigger, a user must have the privilege to execute the data change statement associated with the trigger event. Similarly, to successfully invoke an SQL routine or dynamic compound statement a user must have the EXECUTE privilege on the routine.
Yes.
You can control this behaviour with the
EXECUTE AS
clause of the create statement, as explained here.
The default for triggers is
EXECUTE AS CALLER
where we find
CALLER
Specifies the statements inside the module are executed in the context of the caller of the module. The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module. CALLER is the default for all modules except queues, and is the same as SQL Server 2005 behavior.
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
