BUG: Permissions Problem with Stored Procedure and Table (95287)

SYMPTOMS

After creating a stored procedure that selects from a table and a view into a temporary table, any attempt to execute this new stored procedure by any user other than the owner of the stored procedure results in an incorrect "permission denied" error. The other users getting the "permission denied" error were correctly granted execute permission on this new stored procedure.

The following example demonstrates the problem:

   create table t1 (c1 int)
   create table t2 (c2 int)

   create view v1 as select c2 from t2

   create procedure problem as
      select c1, c2 into #t1 from t1, v1

RESOLUTION

To work around the problem, first create the temporary table, and then insert the data into the temporary table:

   create procedure work_around as
     create table #t1 (c1 int, c2 int)
     insert #t1
        select c1, c2 from t1, v1

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 4.21 and 4.21a. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.