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