In my previous article, I described a privilege escalation scenario involving Azure Arc-enabled SQL Server and a database-level DDL trigger created by a database-scoped principal. This database-level DDL trigger was later fired by a highly-privileged Azure Arc operation.

The situation raises a question I’ve been thinking for a while: why does SQL Server allow trigger code to inherit the privileges of whichever principal happens to fire it?

The problem: trigger code inherits the caller’s privileges

By default, SQL Server executes both DML (data manipulation language) and DDL (data definition language) triggers under the security context of the user whose statement causes the trigger to fire. The trigger author supplies the code, but the future caller supplies the privileges under which that code runs.

This is the default behavior of SQL Server triggers, and it creates a dangerous situation. One principal controls the trigger code, another supplies the execution privileges and, combined, the trigger can then potentially hijack the caller’s authority.

Additionally, a user who creates a trigger does not need permission to perform every operation contained in the trigger. They only need permission to create the trigger – and then an opportunity for a more privileged principal to fire it later.

This is not an undocumented side effect of SQL Server – in fact, it’s the documented default execution model. Microsoft even explicitly warns that this behavior can be exploited to introduce malicious code that later executes under elevated privileges.

So, the question is: why should this be allowed to happen? Is this really the best approach?

The trigger author writes the code, but the caller supplies the privileges

Consider the security relationship created by a trigger. One principal creates or modifies executable code, which remains stored inside the database. Then, later on, another principal performs an operation that causes SQL Server to execute it automatically.

Those two principals may have completely different levels of trust. The trigger author might be an application developer with permissions over a particular table, a database schema administrator, a deployment account, or a principal that can create database-level DDL triggers.

The future caller, meanwhile, might be a DBA, SQL Server Agent, a deployment system, monitoring software, a privileged service account, a cloud-management extension, or a member of sysadmin.

The key here is that, unless an execution context is explicitly specified, SQL Server makes a critical security decision automatically: the stored code executes as the caller. The person supplying the code and the person supplying the privileges are therefore not necessarily the same person – and the privilege available to the code can change depending entirely on who happens to activate it.

How does this differ from ordinary SQL Server stored procedures?

CALLER can make sense for an ordinary stored procedure. A user deliberately invokes a module, and there’s a relatively obvious relationship between the caller and the code being executed:

Triggers, on the other hand, are different. A trigger is an event handler, so the caller doesn’t explicitly execute it. It’s therefore possible for a user to run a legitimate DDL statement and unknowingly activate additional code:

The principal performing the original operation may not even know the trigger exists, especially if they weren’t responsible for its creation (and, in that case, they won’t have control of its source code either). Nevertheless, the default model can allow that stored code to consume the caller’s authority.

Why event-driven code deserves a different trust model

For event-driven execution, that trust relationship is worth challenging. The privileges available to stored executable code should normally be determined by the code’s configured identity, not by whichever unrelated principal happens to generate the event.

Other major database engines have made different architectural choices. For example, Oracle’s documentation states that a trigger always behaves as a definer-rights unit. The trigger therefore executes according to the security context associated with its definer rather than simply inheriting the privileges of the user whose action caused it to fire.

SQL Server already has a safer model in Service Broker

SQL Server’s Service Broker provides a useful comparison as it can automatically start a stored procedure when messages arrive in a queue.

Conceptually, it’s another event-driven execution mechanism: an event occurs, SQL Server automatically invokes stored code, and the code processes the event.

However, Service Broker doesn’t just execute the activation procedure as the user who sent the message. Instead, internally-activated procedures run in a background session distinct from the connection that created the message. The execution identity is associated with the queue’s activation configuration, rather than borrowed from the sender.

EXECUTE AS SELF vs. CALLER in queue activation

When configuring queue activation, SQL Server supports an explicit EXECUTE AS identity:

Microsoft states that the specified database user is the account under which the activation procedure runs. The sender of the message doesn’t automatically donate their security token to the activated procedure. The broader EXECUTE AS documentation makes the difference even clearer:

Service Broker therefore separates three security identities: the principal sending the message, the principal configured to run the activation procedure, and any additional execution identity explicitly declared by the stored procedure.

The sender controls the event and its message content, but doesn’t automatically control the privileges used to process that event. Triggers should, in my opinion, follow the same security principle.

So, while Service Broker is not directly equivalent to DDL triggers – with different execution semantics and compatibility requirements – it does illustrate a security design principle relevant to this discussion.

That being: event-driven code in SQL Server can execute under an explicitly configured identity rather than automatically inheriting the security token of the principal that caused the event.

What would a safer SQL Server trigger default look like?

The execution context of a SQL Server trigger should be determined when the trigger is created or altered, not inherited unpredictably from each future caller.

At minimum, the default should be equivalent to:

EXECUTE AS SELF means the trigger runs as the principal that created or last altered it. SQL Server stores that principal in the module metadata. Under this model, a login with db_ddladmin could still create a trigger, but the trigger would run only with the creator’s database-level privileges.

This ensures that the trigger doesn’t suddenly become more powerful because a sysadmin, Azure Arc, or another privileged service fired it. It’s also a predictable security behavior: the low-privileged principal creates the trigger, runs as low-privileged principal, and the server-level operation remains denied.

A trusted administrator who deliberately needs a trigger to run under another identity could explicitly configure that identity:

or:

…and the decision to grant the trigger additional authority would then be explicit, reviewable, and visible in metadata. Today, however, the dangerous choice is implicit.

Why requiring an explicit execution context would be even better

Changing the default from CALLER to SELF would substantially reduce the risk, but the strongest design would be to require trigger creators to declare an execution context explicitly.

For example:

Or, when legacy caller behavior is genuinely required:

Requiring the clause would force the author to make a conscious security decision, and would also make security reviews easier. An auditor could immediately distinguish triggers intentionally configured to use the caller’s token from triggers constrained to a fixed database principal.

EXECUTE AS CALLER should be an explicit opt-in to a dangerous behavior, not an invisible default inherited by every trigger that omits the clause.

Why is an explicit choice better than a hidden default?

If a trigger explicitly contains WITH EXECUTE AS CALLER, the trust relationship becomes obvious. A DBA or security reviewer can ask:

When CALLER is simply the consequence of omitting an EXECUTE AS clause, that security decision is easier to miss.

Addressing SQL Server backward compatibility

One obvious objection to this: SQL Server has behaved this way for many years, and existing triggers may intentionally depend on caller context.

A trigger might inspect the caller, depend on caller permissions when accessing another object, implement auditing logic based on execution identity, or intentionally produce different behavior for different users. And I do agree that Microsoft can’t just (safely) change every existing trigger in a cumulative update.

However, backward compatibility and permanent preservation of an unsafe default are not the same thing. Some possible transition mechanisms include:

Microsoft has successfully introduced compatibility levels and opt-in security improvements for many other SQL Server behaviors. Trigger execution context should receive the same treatment.

Why Microsoft’s current guidance puts the burden on the wrong party

Microsoft’s current trigger-security guidance advises administrators to review triggers and disable those that could execute under elevated privileges. Useful operational advice, yes, but it’s just treating the symptom rather than the design problem:

Put simply, this security model just doesn’t scale. A safer platform would have stored code constrained by default, eliminating the need for every future caller to defend its privileges against code already present in the database.

The party that creates executable code should define – or be constrained by – the identity under which that code executes. And, a trigger should execute under a deliberate, stable, least-privileged identity – not automatically inherit the authority of whichever user, administrator, agent, extension, or cloud service happens to activate it.

Service Broker demonstrates that SQL Server can implement event-driven execution without lending the sender’s privileges to the receiving code. Triggers should work the same way.

Final thoughts: why SQL Server privilege should be explicit, stable, and reviewable

SQL Server’s trigger engine is behaving as documented. The question I have is whether the documented default remains a good security design.

A trigger is persistent executable code that can be written by one principal, yet activated later by a completely unrelated principal with substantially greater authority. With the default CALLER model, the trigger’s effective privilege is therefore not fixed when the code is created. Instead, it purely depends on who happens to activate it.

This makes the security properties of the code difficult to make sense of. The trigger has simply been given a more powerful execution token, so a database-scoped trigger that is harmless when fired by its creator can suddenly become dangerous when fired by a sysadmin. This should not be the case.

Instead, the privileges of stored executable code in SQL Server should instead be explicit, stable, and reviewable. After all, SQL Server already provides mechanisms to specify an execution context.

What deserves reconsideration is the default. EXECUTE AS CALLER should be a deliberate choice for trigger authors who genuinely require caller semantics – not an invisible security decision made automatically whenever the author says nothing.

References

1. Microsoft Learn – Manage trigger security

2. Microsoft Learn – CREATE TRIGGER (Transact-SQL)

3. Microsoft Learn – EXECUTE AS clause (Transact-SQL)

4. Microsoft Learn – EXECUTE AS (Transact-SQL)

5. Microsoft Learn – CREATE QUEUE (Transact-SQL)

6. Microsoft Learn – TRUSTWORTHY database property