Member Avatar for fcaserio

Can a query running a selected database access a table on other database?
How do I grant this kind of access?
Going further, can a Foreign key be created among diferent databases?

I'm running around this doubts for a while. Any help is welcome.

[]s

  • 2 Contributors
  • 2 Replies
  • 102 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by fcaserio
Member Avatar for MCP

If the user running the query has rights to select the table in the other database, you will be able to. the syntax is something like:

select * from tableA join otherDB.dbowner.tableB on tableA.x=tableB.y

where otherDB = the other database (if it contains a space, enclose it in square brackets like [Other Database])
dbowner = the database owner, usually dbo unless you do use different schemas, or have named them all differently

Nope, you can't create foreign keys across databases. 98% sure of this.. I've never tried, but I seriously doubt it.

Can a query running a selected database access a table on other database?
How do I grant this kind of access?
Going further, can a Foreign key be created among diferent databases?

I'm running around this doubts for a while. Any help is welcome.

[]s

Member Avatar for fcaserio

tks a lot
I was missing the dbowner prefix

[]s

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.