Microsoft KB Archive/95287

= BUG: Permissions Problem with Stored Procedure and Table =

Article ID: 95287

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q95287



BUG# 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 &quot;permission denied&quot; error. The other users getting the &quot;permission denied&quot; 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.

Additional query words: Transact-SQL

Keywords: kbbug kbprogramming KB95287

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.