{"id":112430,"date":"2026-08-28T12:00:00","date_gmt":"2026-08-28T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=112430"},"modified":"2026-08-28T12:28:15","modified_gmt":"2026-08-28T12:28:15","slug":"why-sql-servers-default-trigger-execution-model-is-a-security-flaw-and-what-microsoft-should-do-about-it","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/why-sql-servers-default-trigger-execution-model-is-a-security-flaw-and-what-microsoft-should-do-about-it\/","title":{"rendered":"Why SQL Server’s default trigger execution model is a security flaw – and what Microsoft should do about it"},"content":{"rendered":"\n

In my previous article<\/a>, 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.<\/strong><\/p>\n\n\n\n

The situation raises a question I\u2019ve been thinking for a while: why does SQL Server allow trigger code to inherit the privileges of whichever principal happens to fire it?<\/em><\/strong><\/p>\n\n\n\n

The problem: trigger code inherits the caller’s privileges<\/h2>\n\n\n\n

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

This is the default behavior of SQL Server triggers<\/a>, 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.<\/p>\n\n\n\n

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<\/em> the trigger – and then an opportunity for a more privileged principal to fire it later. <\/p>\n\n\n\n

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

So, the question is: why should this be allowed to happen?<\/em> Is this really<\/em> the best approach?<\/p>\n\n\n\n

The trigger author writes the code, but the caller supplies the privileges<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

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. <\/p>\n\n\n\n

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

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

How does this differ from ordinary SQL Server stored procedures?<\/h2>\n\n\n\n

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

EXEC dbo.DoSomething;<\/pre><\/div>\n\n\n\n
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:<\/p>\n\n\n\n
ALTER TABLE dbo.Customer ADD NewColumn int;<\/pre><\/div>\n\n\n\n
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.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Protect your data. Demonstrate compliance.<\/h3>\n                
\n                                            With Redgate, stay ahead of threats with real-time monitoring and alerts, protect sensitive data with automated discovery & masking, and demonstrate compliance with traceability across every environment.                                    <\/div>\n            <\/div>\n                                            Learn more<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Why event-driven code deserves a different trust model<\/h2>\n\n\n\n
For event-driven<\/a> 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.<\/p>\n\n\n\n
Other major database engines have made different architectural choices. For example, Oracle’s documentation<\/a> states that a trigger always behaves as a definer-rights<\/code> 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.<\/p>\n\n\n\n
SQL Server already has a safer model in Service Broker<\/h2>\n\n\n\n
SQL Server’s Service Broker<\/a> provides a useful comparison as it can automatically start a stored procedure when messages arrive in a queue. <\/strong><\/p>\n\n\n\n
Conceptually, it’s another event-driven execution mechanism: an event occurs, SQL Server automatically invokes stored code, and the code processes the event.<\/p>\n\n\n\n
\n    
\n\n
You may also be interested in:<\/strong><\/p>\n\n\n\n
Service Broker Foundations Workbench<\/a><\/p>\n\n\n\n
Service Broker Advanced Basics Workbench<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
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\u2019s activation configuration, rather than borrowed from the sender.<\/p>\n\n\n\n
EXECUTE AS SELF<\/code> vs. CALLER<\/code> in queue activation<\/h3>\n\n\n\n
When configuring queue activation, SQL Server supports an explicit EXECUTE AS<\/code> identity:<\/p>\n\n\n\n
CREATE QUEUE ExpenseQueue\nWITH ACTIVATION\n(\n    PROCEDURE_NAME = dbo.ProcessExpense,\n    MAX_QUEUE_READERS = 5,\n    EXECUTE AS { SELF | 'user_name' | OWNER }\n);\nGO<\/pre><\/div>\n\n\n\n
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<\/code> documentation<\/a> makes the difference even clearer:<\/p>\n\n\n
\n