BUG: Permissions Problem with Stored Procedure and Table

• Microsoft SQL Server for OS/2, version 4.2
• Microsoft SQL Server version 4.2x

BUG# OS/2: 1581 (4.2)
       NT:   94 (4.2) 

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 

WORKAROUND

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 SQL Server version 4.2 for OS/2 and 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.

Additional query words: Transact-SQL

Keywords          : kbprg kbbug4.20 kbbug4.21 kbbug4.21a SSrvServer SSrvWinNT 
Version           : 4.2 | 4.21 4.21a
Platform          : OS/2 WINDOWS 
Issue type        : 

Last Reviewed: March 16, 1999