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

sql-server sql-server-2008 tsql triggers database-security
Chris Cannon picture Chris Cannon · Apr 5, 2012 · Viewed 11.2k times · Source

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.

Answer

Matten picture Matten · Apr 5, 2012

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.

Related questions

How can I edit values of an INSERT in a trigger on SQL Server?

I have the table Tb ID | Name | Desc ------------------------- 1 | Sample | sample desc I want to create a trigger on INSERT that will change the value of the inserting Desc, for example: INSERT INTO Tb(Name, Desc) VALUES ('x', 'y') Will …

Read More
SQL Server: check whether a Trigger is Enabled or Disabled?

How we can see which Trigger is Enabled or Disabled in SQL Server 2008?

Read More
How do I prevent a database trigger from recursing?

I've got the following trigger on a table for a SQL Server 2008 database. It's recursing, so I need to stop it. After I insert or update a record, I'm trying to simply update a single field on that table. Here's …

Read More