How do I select user information with multiple databases using ASP & VB.NET?

Question

Okay here it goes . . . I have a web application I am developing in Microsoft Visual Web Developer using ASP, VB.net, etc. that will allow "Sellers" to post ads for "Buyers" to view. When the Buyer searches for a product, each result needs to display the Seller's name, and the product's information. The problem being that the table that holds the various product information is located in one database and the user/membership information is stored in another database.

I'll provide an example. In this case the Buyer is searching for all ads for a particular book.

1. Each ad is recorded in database db_1 and table [Results] with columns for ID_USER, and ID_BOOK.
2. Book information is stored in db_1 [Books] with columns for ID, TITLE, AUTHOR.
3. User information is stored in a separate database db_2 and table [Users] with columns for ID and NAME.

Given that, how would I do something like the following:

---

SELECT u.Name [Seller]
, b.Title [Title]
, b.Author [Author]
FROM db_1.Results r
INNER JOIN db_1.Books b
ON b.id = r.id_book
INNER JOIN db_2.Users u
ON u.id = r.id_user
WHERE b.Title like 'Some Book Title'

---

If these tables were in the same database (which I'm now wishing I had done), then I would easily run an inner join or some other select statement to pull only the relevant data. I've also considered creating datasets and joining them (e.g., http://www.vb-helper.com/howto_net_dataset_foreign_key.html), but wouldn't that be way too server-heavy?

I'm half decent with SQL statements, but all my knowledge comes from Google and from practice so if I am ignoring some crucial caveat that every classically trained programmer is aware of, I apologize. Thank you in advance for the help.

UPDATE: I thought I would give an update on the situation. The problem actually did not have anything to do with the permissions. The problem was that VWD could not find the object. I solved this issue by using a CROSS JOIN and explicitly stating the directory path of the .mdf file in the select statement. Thank you again for all your help.

Answer 1

Your query will work with a minor change, as @rs above indicates. The portion dbo in the middle between the database name and the table name is the owner name of the objects. If the owner has rights to both schemas, and your user ID has rights to select from both databases, the query will work.

SELECT u.Name [Seller]
, b.Title [Title]
, b.Author [Author]
FROM db_1.dbo.Results r
INNER JOIN db_1.dbo.Books b
ON b.id = r.id_book
INNER JOIN db_2.dbo.Users u
ON u.id = r.id_user
WHERE b.Title like 'Some Book Title'

Answer 2

Here are simple steps to use same user in both database and grant select permission using sql server management tool

• go to db1 > security > users > new user.
• Click login name and browse name and select user you want to use
• go to db2 > security > users > new user.
• Click login name and browse name and select same user from db1 above
• Go to user table in db2, right click and go to properties click permissions and click search button to find same user from above and select that user
• In User or roles box select user and then go to permissions panel below and check Grant for select and click ok.

Repeat last step for all tables you want database user to have access from database 2