PRB: Inconsistent Messages Returned When the EXEC Statement Is Run on a Table
ID: Q173335
|
<![CDATA[
-------------------------------------------------------------------------
The information in this article applies to:
- Microsoft SQL Server, version 6.5
-------------------------------------------------------------------------
SYMPTOMS
========
If an EXECUTE is attempted on a table instead of a stored procedure, the
error message that should be returned is:
The request for procedure 'TABLENAME' failed because 'TABLENAME' is a
table object.
However, the following error may be returned instead:
EXECUTE permission denied on object TABLENAME, database DATABASE, owner
OWNER
WORKAROUND
==========
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.
MORE INFORMATION
================
The following scenario illustrates the problem:
If User A (USERA) owns Table A (TABLEA) and runs EXEC USERA.TABLEA, the
error message returned is:
The request for procedure 'USERA.TABLEA' failed because 'USERA.TABLEA'
is a table object.
However, if User B (USERB) runs EXEC USERA.TABLEA, the error message
returned may be:
EXECUTE permission denied on object TABLEA, database DATABASE, owner
USERA
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:
EXECUTE permission denied on object TABLEA, database DATABASE, owner
USERA.
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
]]>
Keywords : kbprg kbusage SSrvProg SSrvVisB
Version : 6.5
Platform : WINDOWS
Issue type : kbprb
Last Reviewed: September 3, 1997