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. This rule exists because 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, Adaptive Server 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.
If a user without sa_role privileges executes the stored procedure, the user should qualify the table name as tempdb.guest.mytab. If an object name in a temporary database is already qualified with the default owner’s name, a query such as the following may not return a correct object ID when users without sa_role privileges execute the stored procedure:
select object_id ('tempdb..mytab')
To obtain the correct object ID when you do not have sa_role privileges, use the execute command:
exec("select object_id('tempdb..mytab')")
