People always ask…if ownership chains work the way they do, why do they not work with dynamic SQL? And how about .NET procedures and ownership chains?
Dynamic SQL is supported in nearly every database I've run across, but bad dynamic SQL has "issues", to put it mildly. If you create your dynamic SQL via string concatenation rather then parameterization (there are parts of the SQL statement that cannot be parameterized) and your strings come from user input, you will run up against a security problem known as SQL injection. Users can enter strings in applications, that, when used with concatenation, do things that you never intended your statement to do. You need to be VERY aware of the hazards of SQL injection before even *thinking* about dynamic SQL.
SQL Server procedural code (stored procedures, UDFs, and triggers) runs as the caller of the procedure. Because dynamic SQL can be dangerous, it doesn't go by the ownership chaining rule. Access to database objects in dynamic SQL is always checked. Against the original caller's permissions. In SQL Server 2005, the EXECUTE AS clause can allow procedural code to run as a principal other than the caller, which permits a way to address this behavior (other than the usual way, which was to yell “don't use dynamic SQL“ loudly).
When .NET procedural code uses the SqlServer provider to issue SQL statements, these are *dynamic* SQL to the engine. Ownership chains do not apply. I've had difficulty using EXECUTE AS with .NET code in betas, hoping that the new betas fix this. This mostly matters for procedures and triggers. You usually don't do data access in UDFs and user-defined aggregates don't have an EXECUTE AS clause at all; you shouldn't be doing data access in UDAggs anyway.