SQL Interview: 53 Dynamic SQL and Ownership Chaining

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Security Level: Advanced

Question:

You have a table named Sales.Customers that is owned by dbo. You also have a stored procedure named Sales.FindCustomers that searches the Sales.Customers table.

A user Terry is given permission to the procedure and can successfully execute the procedure.

The procedure is modified and now includes some dynamic SQL. After the change is made, Terry can no longer successfully execute the procedure. SQL Server complains that Terry does not have access to Sales.Customers.

What is the issue and how can you correct the situation to allow Terry to use the procedure, without granting Terry direct access to the table?

Answer:

With ownership chaining, if the procedure owner is the same as the table owner, then no check is made for permissions on the table.

Dynamic SQL statements break that ownership chain.

You can correct the situation by adding a WITH EXECUTE AS OWNER clause to the procedure definition.

2025-06-05