Inside a stored procedure, object names used with create table and dbcc must be qualified with the object owner’s name, if other users are to use the stored procedure.
Object names used with other statements, like select and insert, inside a stored procedure need not be qualified, because the names are resolved when the procedure is compiled.
For example, user “mary,” who owns table marytab, should qualify the name of her table with her own name when it is used with select or insert, if she wants other users to execute the procedure in which the table is used. Object names are resolved when the procedure is compiled, and stored as a database ID or object ID pair. If this pair is not available at runtime, the object is resolved again, and if it is not qualified with the owner’s name, the server looks for a table called marytab owned by the user “mary” and not a table called marytab owned by the user executing the stored procedure. If it finds no object ID “marytab,” it looks for an object with the same name owned by the database owner.
Thus, if marytab is not qualified, and user “john” tries to execute the procedure, SAP ASE looks for a table called marytab owned by the owner of the procedure (“mary,” in this case) or by the database owner if the user table does not exist. For example, if the table mary.marytab is dropped, the procedure references dbo.marytab.
If you cannot qualify an object name used with create table with the object owner’s name, use “dbo,” or “guest” to qualify the object name.
If a user with sa_role privileges executes the stored procedure, the user should qualify the table name as tempdb.dbo.mytab.
select object_id ('tempdb..mytab')
exec("select object_id('tempdb..mytab')")