How can default schema change in stored procedure ?

Hello,

How can default schema change in stored procedure ?

For Example:

There are two user 'User1', 'User2' in TestDb Database.
These users default schema is same name, like 'User1's default schema is 'User1', and 'User2's default schme is 'User2'.
And each users have 'Table1' table, like [User1].[Table1], [User2].[Table1]

In this enviroment,
query 'SELECT * FROM [Table1]' refer default schema of execute user.
like 'User1' execute 'SELECT * FROM [User1].[Table1]'.

But if dbo create a stored procedure below, default schema doesn't work.

CREATE PROCEDURE SelectTable1
AS
SET NOCOUNT ON
SELECT * FROM Table1
GO

When User1/User2 execute this stored procedure, error happend because Table1 not found.

So, I want to change default schema in stored procedure to current users default schema.
EXECUTE AS CALLER is change current user principal only, this doen't change default schema.

Regards,

This is not possible to do in TSQL right now without using dynamic SQL for the query inside the stored procedure. For EXECUTE AS CALLER the unqualified object names resolve against the schema for the owner of the SP and not the caller. This is known issue and there have been requests to provide the facility to resolve object name against the invoker of the SPs. Oracle for example allows you to specify this when creating PL/SQL SPs.