Microsoft KB Archive/250533

From BetaArchive Wiki
Knowledge Base

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


  • Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q250533

BUG #: 18943 (SQLBUG_65)


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.


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.


  • Let the same user own both databases, the stored procedure and the permanent table.


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

Additional query words: temp table

Keywords: kbbug kbpending KB250533