Qualifying Names Inside Stored Procedures

Inside a stored procedure, object names used with the statements ALTER TABLE, CREATE TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX, DROP INDEX, UPDATE STATISTICS, and DBCC must be qualified with the object owner's name if other users are to make use of the stored procedure. For example, user Mary, who owns table marytab, must qualify the name of her table when it is used with one of these statements if she wants other users to be able to execute the procedure in which the table is used.

This rule is necessary because object names are resolved when the procedure is run. If marytab is not qualified and user John tries to execute the procedure, SQL Server looks for a table called marytab owned by John. This example shows the correct usage; it tells SQL Server to look for a table called marytab owned by Mary:

create proc p1
as
create index marytab_ind
on mary.marytab(col1)

Object names used with other statements (for example, SELECT or INSERT) inside a stored procedure need not be qualified because the names are resolved when the procedure is compiled.