Tried Jeff Moden's suggestion to use "context switching" but I cannot get this to work based on three schemas and a dbo schema stored procedure, where the schema of the table is not specified. Any suggestions?

EXECUTE AS USER = 'CarlFederl1'

select * from Foo

exec dbo.foo_list

revert

go

Output is:

name

------

ONE

(1 row(s) affected)

Msg 208, Level 16, State 1, Procedure foo_list, Line 2

Invalid object name 'foo'.

-- Reproduction SQL statements

-- create database and logins not included.

USE CarlFederl

GO

create schema Schema1 authorization dbo;

go

create schema Schema2 authorization dbo;

go

create table Schema1.Foo

(namevarchar(255) not null );

create table Schema2.Foo

(namevarchar(255) not null );

CREATE USER CarlFederl1 FOR LOGIN CarlFederl1 WITH DEFAULT_SCHEMA=Schema1;

CREATE USER CarlFederl2 FOR LOGIN CarlFederl2 WITH DEFAULT_SCHEMA=Schema2;

insert into Schema1.Foo (name) values ('ONE');

insert into Schema2.Foo (name) values ('TWO');

go

create procedure dbo.foo_list as

select * from foo

go

grant execute on dbo.foo_list to CarlFederl1 , CarlFederl2;

grant select on schema1.foo to CarlFederl1 ;

grant select on schema2.foo to CarlFederl2;

go