set user's default database/schema in Sybase IQ

database sybase sap-iq
Daniel Robertus picture Daniel Robertus · Jul 24, 2013 · Viewed 7.9k times · Source

i googled it and find nothing. Short story, i created a user and granted to a table in my SyBase. but when i try 

select * from table1

it didn't work. Error show Permission denied: you don't have permission to select from "table1" and i try add dbname before table name like this and it works : 

select * from dbname.table1

i suspect that user default database is something else so i want to set dbname to his default database. Anyone know how to do this?

Answer

Graeme Perrow picture Graeme Perrow · Jul 24, 2013

This has nothing to do with database names (or login policies). Given your comment that "dbname" is actually the user who owns the table, here's what's happening.

When you specify a table name without an owner, the server has to figure out which table you mean. It first looks for a table that you own with that name. If it doesn't find one, it looks for tables owned by any groups that you are a member of. I suspect that one of these groups has a table named "table1" that you do not have permission to select from.

When you specify a table name with an owner, the server knows exactly which table to use. Since you do have permission to select from that table, you get the results you want.

Related questions

Sybase date comparison - Correct format?

I'm pretty new to Sybase and am writing a query to return results after a specified date, and also before a specified date. MM/DD/YYYY format At the moment im doing.. SELECT * From aTable WHERE afterDate >= 08/07/2013 AND beforeDate &…

Read More
SQL SELECT INSERT INTO Generate Unique Id

I'm attempting to select a table of data and insert this data into another file with similar column names (it's essentially duplicate data). Current syntax as follows: INSERT INTO TABLE1 (id, id2, col1, col2) SELECT similiarId, similiarId2, similiarCol1, similiarCol2 FROM …

Read More
How to get sybase table column name and its datatype and order by?

There are multiple table in my sybase database. I want to know the column name and datatype of a given table like (myOrder table). How can I do this? Below script I found on stackoverflow From a Sybase Database, how …

Read More