CODEWITHSUNDEEP
databasejoinsybasesap-asemultiple-databases
iWantSimpleLife
iWantSimpleLife

Reputation: 1954

How to join tables in different database on the same Sybase server

Am working on Sybase ASE 15.5.

I have 2 databases created in the same server, "DatabaseA" and "DatabaseB". The database owner is "User".

Logging in as "User" I created a table in "DatabaseB", called "TableA".

Now, my user has access to both database, but the default database is "DatabaseA".

This is sucessful when i login to DatabaseA:

USE DatabaseB
GO
SELECT * from DatabaseB.User.TableA
GO

But this is not:

USE DatabaseA
GO
SELECT * from DatabaseB.User.TableA
GO

It tells me that there is "No such object or user exists in the database".

I have Googled and most sites say that if the user has rights, then you only need to append the database and owner name to the table to access it. But it does not seems to work for my case.

I have tried creating a non DBO user "User2", and assigning it select rights using

GRANT SELECT ON DatabaseB.User.TableA to User2

and sp_helprotect shows that the rights is there for this user. But the results are exactly the same as when i query it with User.

Below is the result from sp_helprotect

grantor | grantee | type    | action   | object   | column | grantable
'User'  | 'User2' | 'Grant' | 'Select' | 'TableA' | 'All'  | 'FALSE'

Is there anything configuration or setting that needs to be checked to enable this?

EDIT (22 July 2015)

Just discovered something. There are a few tables with DatabaseB that i can access from DatabaseA, but not all tables.

For example, there is TableA, TableB, TableC, and TableD in DatabaseB. Out of which TableB and TableD can be queried from DatabaseA using 

USE DatabaseA
GO
SELECT * from DatabaseB.User.TableB
GO
SELECT * from DatabaseB.User.TableD
GO

which is sucessful. And 

USE DatabaseA
GO
SELECT * from DatabaseB.User.TableA
GO
SELECT * from DatabaseB.User.TableC
GO

fails.

Help!!!

Upvotes: 3

Views: 9167

Answers (3)

John
John

Reputation: 159

Having a login and user caled "USER" is not a good idea as it is also the name of the system function that returns the name of the current user. Can you try the same query using a different user altogether (eg. "bob")?

You may need to grant the appropriate permissions to bob but at least there won't be any confusion about user names.

This is further to RobV's comments (I don't have enough rep to add comments though!)

Upvotes: -1

RobV
RobV

Reputation: 2378

The ASE server login seems to be 'User' -- a login is the thing that needs a password. This is not the same as the database user inside a database. THis mapping is established with 'sp_adduser'.

To resolve your problem, you need to figure out which DB user you are. Run the following:

use DatabaseA
go
select user_name() user_in_A, suser_name() login_name
go

use DatabaseB
go
select user_name() user_in_B, suser_name() login_name
go

The output of these queries should help you move forward.

Upvotes: 0

barun
barun

Reputation: 403

try SELECT * from DatabaseB..TableA to fetch the result from different database

also you can do below 

use DatabaseB
SELECT * from TableA

you must at least have read access to the database .

Just another example : Just another example:

select tabA.*,tabC.* from DatabaseB..TableA tabA, DatabaseA..TableC tabC
where tabA.xxx = tabC.xxx

Upvotes: 2

Related Questions