Microsoft KB Archive/173335

= PRB: Inconsistent Messages Returned When the EXEC Statement Is Run on a Table =

Article ID: 173335

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q173335



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

Additional query words: asp visual basic vb active data objects

Keywords: kbprogramming kbprb kbusage KB173335

-

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

© Microsoft Corporation. All rights reserved.