[dba-SQLServer] Creating joins across multiple databases

I'm including the latest article from Arthur Fuller on the Builder.com 
SQL Newsletter because I hope it can bring forward some positive 
discussion.  Plus I'm sure many of us have dealt with this particular 
situation or will deal with it in the future.

/<BUILDER.COM SQL Newsletter>/*
Creating joins across multiple databases*

Enterprise databases often comprise several databases--one from 
production, another from marketing, and so on. All of the databases rely 
on a certain amount of common data, which is frequently copied to each 
database that needs it.

However, this copying routine creates a new problem: skew. Where there 
is duplication, there is at least the opportunity for skew. The solution 
to this problem is often replication, or scheduled jobs that update each 
copy of the common data from one "master" database. This approach works, 
but sometimes a better method is to move all the common data into one 
database, and then access it as needed from the other databases. This 
eliminates both the skew and the replication jobs.

This approach typically requires joining some local tables to the common 
tables. For example, suppose you have two databases: Common and Sales. 
The Customers table resides in the Common database, while the Orders 
table resides in the Sales database. You want to create a query that 
lists the Customer information along with the Order information. How do 
you join these two tables? The answer lies in prefixes.

SQL Server uses a dot-delimited nomenclature that extends outward to the 
database and the server. To specify a column in a table in another 
database, name the other database:

SELECT CustomerID FROM Common.Customers

To join a table from the Common database to a table in the current 
(Sales) database, use this same nomenclature:

SELECT * FROM Common.Customers
INNER JOIN Orders
ON Common.Customers.CustomerID = Orders.CustomerID

Not every organization's application domain requires multiple databases. 
If your organization does require multiple databases, it's easy to 
create joins across databases.

/Arthur Fuller has been developing database applications for more than 
20 years. He frequently works with Access ADPs, Microsoft SQL 2000, 
MySQL, and .NET./
/<BUILDER.COM SQL Newsletter>/

Yes it is easy enough to join accross multiple databases and even 
accross several Sql Server linked servers :)   (very cool)... however my 
concern and one that was not addressed in this article is the security 
aspect of it... say you just have a user who calls the report... does 
the user need to use an application who's using an application role and 
thus gets better than normal priviladges on the server?  I have not been 
successful in adding new users to another database w/o also having to 
takes some hard steps; my solution was to ... Create VIEWS on the target 
database... add the user to that 2nd database, then give explicit selet 
crights on the views and then join accross from db2 to db1 via the sproc 
using the views instead of the tables... so while the data is not 
duplicated, the user is, and now has rights to base views that represent 
tables :( , so because I think I might be doing this wrong.. I'm hoping 
Arthur or someone else will chime in with a few clues :)

Thanks,

-- 
-Francisco