Sybase Stored procedure error Select permission denied on object

Yesterday we finally resolved a strange issue which was troubling us for a few hours.
We had a web page which was calling a newly created stored procedure. This stored procedure was returning results, but most fields of the result set were NULLS.

While we know that there is no chance for the data to go missing, we were left with the following options:

1. Execute just the query used inside the stored procedure
   
2. See the IO statistics to see if the correct tables are internally mapped (we had faced a similar issue a few weeks back, Check it here)

On running the IO statistics, we found that there was a warning - Server Error Number 10330, Severity 14. Select permission denied on object dbName..tableName.

We were logged into the database with very limited access and permissions and so after getting a login which had sufficient Select and Exec permissions, we proceeded to find out and fix the issue.

1. There were two databases involved. Let us call it db1 and db2
2. Stored procedure created in db1, selects from a table in db2 (db2..tablename)
3. The db1 stored procedure was granted exec permissions to user group Group1
4. The login used from the UI connection string belonged to the db1..group1 but did not belong to db2..group1
5. The reason why the old stored procedure worked was because – that procedure was created by a user who had sa rights – and Sybase applies the rights of the owner to the queries inside it

Adding the login user (from the UI connection string) to db2..Group1 resolved this issue.

Sybase stored procedure bug!?! Due to renamed table

We were investigating an issue with a web page – that displays data from a Sybase database. The problem was simple – the data was not as expected. The values were missing.

When we queried the actual table (say TableA) from which the stored procedure selects, the information was there. But on running the stored procedure, there would be no results.

We first suspected the parameters that were being passed in – but they were fine.

We then ran the stored procedure with IO statistics On and traced the culprit. The stored procedure was selecting records from a totally different table (TableA_old). The logical explanation is this:

Somebody renamed TableA to TableA_old. Thereby breaking all stored procedures, views depending on it. Later this somebody made another table by the same name TableA. But that does not fix the things that were broken earlier by the renaming.

The stored procedure (which is precompiled) was still pointing to the old table since it was referencing the table using id’s used internally by the database.

We saw if there was an option to recompile as I remembered that there was a sp_recompile, but that did not help. I played around with sp_recompile and it did mark the table to cause all associated sp’s to be recompiled the next time it was run. But when I ran the stored procedure again, it recompiled (there were 2 IO Stats entry), but both were pointing to the old (renamed) table only.

We then dropped and re-created the stored procedure and that fixed it all.

Tthe Sybase version I was working on was quite an old one:
“Adaptive Server Enterprise/12.5.3/EBF”

Here is a quick way to find out what version you are in. SELECT @@version
To just give some code examples to reproduce this error, try this on some old version of Sybase:

CREATE TABLE tbl1
(
name VARCHAR(10)
)

CREATE PROCEDURE sp_tbl1
AS
SELECT name FROM tbl1

SET STATISTICS IO ON
EXEC sp_tbl1

/*
**RESULT WOULD BE:
Table: tbl1 scan count 1, logical reads: (regular=1 apf=0 total=1),
physical reads: (regular=1 apf=0 total=1), apf IOs used=0
**/

SET STATISTICS IO OFF
sp_rename 'tbl1','tbl1_old'

CREATE TABLE tbl1
(
name VARCHAR(10)
)

SET STATISTICS IO ON
EXEC sp_tbl1
/*
**RESULT WOULD BE:
Table: tbl1_old scan count 1, logical reads: (regular=1 apf=0 total=1),
physical reads: (regular=0 apf=0 total=0), apf IOs used=0
**/

DROP PROCEDURE sp_tbl1
DROP TABLE tbl1
DROP TABLE tbl1_old

Output Parameter from stored procedures in Sybase ASE

I have never worked with Sybase, until recently – since one of my current employer’s clients is having their data almost fully in Sybase. A really early version of Sybase too.

Now there are a slew of things that you would have to re-adjust to when you are working with Sybase, especially after working extensively and entirely in SQL. Sybase is case sensitive and we are currently given to work with SQL Advantage which is a pretty naïve tool and stands no where close to my favorite Query Analyzer. But of course, it was built for the yesteryears. If there is anyone I should blame, it must be the client for still not upgrading!

So, as and when I learn something, I would try to note it down here.

First thing I learnt –
Problem:
Output parameters on Sybase stored procedures would not return back to your recordset.

Usual workarounds
Will “Select” the value and get it as part of the result set

Actual Fix
Set the CursorLocation property of the Connection object to adUseClient (adovbs.inc value 3), like this:

objConn.CursorLocation = adUseClient

OR

objConn.CursorLocation = 3