Microsoft KB Archive/250533

= BUG: Temp Table in Stored Procedure: SQL Does Not Realize Ownership Chain Broken Until Restarted =

Article ID: 250533

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q250533



BUG #: 18943 (SQLBUG_65)



SYMPTOMS
Consider the following scenario:  DatabaseA                           DatabaseB (Owned by AdminA)                   (Owned by AdminB) |                                    |     |                                     | Stored Procedure                        Table1 (Owned by SA)                       (Owned by AdminB) :                                    | create table #temptable                    | update #temptable                         | set column1 = Table1.column1              | from #temptable, DatabaseB..Table1 ________| where ... : select * from #temptable

If the System Administrator (SA) grants Execute permission on this stored procedure to a user (User1) who is just another user in DatabaseA and DatabaseB, SQL server incorrectly allows User1, who has no select permission on the permanent table (Table1) in DatabaseB, to execute the stored procedure after it is initially created. Not until the server is stopped and restarted does the server correctly deny User1 from executing the stored procedure due to insufficient privileges on the permanent table in DatabaseB.



WORKAROUND
There are two workarounds for this problem. You can use either of the following:
 * Let SA be the owner of all the objects in the server.

-or-
 * Let the same user own both databases, the stored procedure and the permanent table.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5.

Additional query words: temp table

Keywords: kbbug kbpending KB250533

-

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

© Microsoft Corporation. All rights reserved.