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

ID: Q173335

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

Additional query words: asp visual basic vb active data objects

Keywords : kbprg kbusage SSrvProg SSrvVisB
Version : winnt:6.5
Platform : winnt
Issue type : kbprb