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.

SqlDbx - Really Cool

I was asked to work in a project migrating from classic ASP to .Net and the database used was Sybase ASE. The only option for DB coding, was to use the pre-installed SQLAdvantage.

The first thing I did was to search for a better tool and after getting SqlDbx I knew, I've got the best.

Guys in my team were thrilled at this product. I have recommended this one to my company's IT Tools department and I am sure each one of us would get the professional edition real soon.

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

Sybase patIndex string/column validation

Recently one of my colleagues asked me if there is any way we can validate a given string or a column in a Sybase table for containing only alphanumeric characters. Asking what exactly she wanted, I came to know that she wanted a string to just contain a-z,A-Z,0-9 and no more.

There was no IsAlpha function in Sybase AFAIK. So the suggestion was to go the other way – use “patIndex” to cancel out all the special characters.

Declare @variable1 varchar(20)
Set @variable1 = "S12345[67D"
Select patindex('%[~!@#$%^&*()_+{}"?<>:/.,'';[]\`=-]%',@variable1)

Result would be 7 (the string assigned to @variable1 has a ‘[’ at position 7)

patIndex would return a value greater than 0 if any of the special characters specified is present in the passed variable or a column value. There could be a better/simpler way of doing this, but this one quickly served the current purpose of hers.

However, a better way of doing it would be:
Declare @variable1 varchar(20)
Set @variable1 = "S12345[67D"
Select patindex('%[^a-z,A-Z,0-9]%',@variable1)

Result would be greater than zero if any character other than the one specified in the range is in the variable, in our case again 7.