Why does our ownership chain break in T-SQL stored procedures?

We have two stored procedures in the same Azure SQL DB, [a].[doA] and [b].[doB]

User has SELECT, INSERT, UPDATE, DELETE, EXECUTE on schema [a], and nothing on other schemas.

All db schemas have owner [dbo]

Both procedures do selects, inserts, updates on other schemas, and both run dynamic queries on other schemas with EXEC @sql syntax (we don't use sp_excutesql, as it breaks the ownership chain).

[a].[doA] also calls [b].[doB] in some cases, and in those cases the ownership chain seems to break in the dynamic sql part of [b].[doB], we receive an error when executing the dynamic query there: The SELECT permission was denied on the object 'table', database 'db', schema 'c'

We are very surprised, as ownership chain works in all other cases, both [a].[doA] executing dynamic sql on schema [c], and [b].[doB] running queries on schema [c].

Why is this the case? How can we prevent it?

We already tried the following workarounds, both didn't work:

• we granted user EXECUTE on schema [b]
• we moved [doB] to schema [a]

Edit: I do understand that EXEC @sql is supposed to break the ownership chain just like sp_executesql, but in our environment, whenever we use sp_executesql, our ownership chain breaks, and when we use EXEC @sql, it only breaks in this one case

We have changed [b].[doB] to EXECUTE AS OWNER, and it works now.

Thanks David Browne for the suggestion!

Thanks Thom A for your well researched answer, we ended up going in another direction!

Thanks for everyone suggesting to use sp_executesql! I do agree that it is a good practice, and would normally always use it, but in our case (I also don't understand why) it causes permission problems. As the dynamic queries only need a numeric parameter at runtime, in this special case we decided that EXEC @sql was an acceptable solution.