We are running SQL Server 2008 using Windows Authentication. I have a couple of stored procedures created in the dbo schema for reporting. The users are executing the SPs from a VB front end. The first sp executed is to create a tmp table in the logged on users default schema. No problem, the table is created. The next stored procedure is called to insert into that table and an error is thrown stating it can't find the object of the tmp table. Looking in SMS, the table is there in the correct database, qualified by the correct username. The stored procs just cannot insert into or select from that table. I found a small blurb about non qualified objects referenced in a stored proc are only searched for in the stored proc owners schema. We've tried Execute AS with various options without success. We can't hardcode the qualifier in the stored procs because we want several users to run the report at the same time. Is there anyway around this situation other than rewriting the report process?
Thanks in advance for your help.