SQLPrepare and SQLExecute returns Permission Denied in SQL 2005

Question

I am trying to determine what has changed in SQL Server 2005. We have a application that has used this SQLPrepare and SQLExecute pairs for several years now and all of the sudden on 2005 it fails with a -1 error.

Code Sample:

odErr = (ODErr)SQLPrepare (hstmt, (PTR)gODCDA->sqlStr, SQL_NTS);

if(!odErr){

odErr = (ODErr) SQLExecute(hstmt);

}


The Statement was "Select * from dbo.tbl1234 where 1=0"

This is a standard way we use to get all sorts of properties and test for file existence of a table. This prepare statement FAILS with a -1 error and the SQL extended error says:

Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'tbl1234', database 'mydata', schema 'dbo'."    
(which is entirely untrue).

Can someeone tell me if there are new parameters to be set, or if an API has changed, or what could cause this to fail only in 2005.

Answer 1

There are no changes in the APIs. Most likely the server is telling the truth and the necessary permissions really are missing for the login used on the connection. 

Answer 2

Could you try using "Select * from tbl1234 where 1=0" (i.e. - without the 'dbo.' prefix)? Perhaps that will solve your issue.

Answer 3

The error you got was from server (note [SQL Server] in the msg), it looks like select permission was denied for the login user.

Try the following 2 things to confirm that:

1. connect to the server using SQL Manangement studio, osql or sqlcmd, run the select query from the client tool. If you still see the error, the problem should not be related to SQLPrepare/SQLExecute.

2. Use sp_table_privileges to see the permissions on the tbl1234 table (please login as sa).