SQL Server 2008 - Does a trigger run with the same

问题:

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.

回答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.

回答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