Tuesday, January 13, 2009

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.

No comments: