SQL Server 2008 - Does a trigger run with the same permissions as the login/user?

Question

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.

Solution 1

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.

Solution 2

by default yes, but you can change that using

WITH EXECUTE AS

on the trigger definition

http://msdn.microsoft.com/en-us/library/ms188354(v=sql.100).aspx