Microsoft KB Archive/173335
Article ID: 173335
Article Last Modified on 10/3/2003
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q173335
If an EXECUTE is attempted on a table instead of a stored procedure, the error message that should be returned is:
However, the following error may be returned instead:
Programs that rely on the error message should check for both messages. In ADO, a COMMAND object may be used to indicate that the object is a table and not a stored procedure.
The following scenario illustrates the problem:
If User A (USERA) owns Table A (TABLEA) and runs EXEC USERA.TABLEA, the error message returned is:
However, if User B (USERB) runs EXEC USERA.TABLEA, the error message returned may be:
Programs that only test for one of the messages may not function correctly if the other message is returned.
For example, the following ADO 1.0 code runs correctly under the user context of USERA, but may fail under the user context of USERB and returns the error:
This is because under USERB, ADO first tests if USERA.TABLEA is a stored procedure by attempting to execute it. However, due to the EXECUTE permission denied error message, it concludes that USERA.TABLEA is a stored procedure, and does not continue on to test if TABLEA may be a table.
'Assumes USERA owns TABLEA and has granted 'select permission on TABLEA to USERB Dim conn Dim rs Set conn = CreateObject("ADODB.Connection") conn.Open "SQLDSN", "USERB", "PASSWORD" Set rs = CreateObject("ADODB.Recordset") rs.Open "USERA.TABLEA", conn rs.Close conn.Close
Additional query words: asp visual basic vb active data objects
Keywords: kbprogramming kbprb kbusage KB173335