CREATE TABLE XYZ.TABLE1 (col1, col2);
CREATE PROCEDURE dbo.PROC1 AS SELECT * FROM XYZ.TABLE1;
CREATE PROCEDURE dbo.PROC2 AS EXEC sp_excutesql N'select
* from XYZ.TABLE1';
IF the login user X has rights to dbo.Proc1 & dbo.Proc2
and no rights on XYZ.TABLE1 then
X can execute dbo.Proc1 but fails to execute dbo.Proc2.
Why does the ownership chain break on calling "EXEC"
or "sp_excutesql" stored procedures?
Does it mean, "EXEC" and "sp_executesql" stored
procedures are not owned by "dbo" owner? Who is the owner
of these system procedures?