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.
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.
Here are simple steps to use same user in both database and grant select permission using sql server management tool
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 Repeat last step for all tables you want database user to have access from database 2