[dba-SQLServer] Creating joins across multiple databases

copy paste...
Djabarov, Robert said the following on 4/1/2004 10:51 AM:

>Based on what I've seen so far, it's not a good idea to do cross-db
>joins, and not only because of security issues as Francisco mentions,
>but also because of questions like "Who the heck is this guy and what is
>he doing in my swamp?" which immediately start popping up, which leads
>to finger-pointing when there is a slow-down or outage.  It also
>violates database designation (DSS, OLTP) when it's OLTP for a native
>user while becoming a DSS for the new-comer.
>
>BTW, Francisco, did you copy the article or retyped it?  If you copied
>it, I see a minor problem with Author's syntax...
>
>
>Robert Djabarov
>SQL Server & UDB
>Sr. SQL Server Administrator
>Phone: (210)  913-3148
>Pager: (210) 753-3148
>9800 Fredericksburg Rd. San Antonio, TX  78288
>www.usaa.com
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
>Francisco H Tapia
>Sent: Thursday, April 01, 2004 12:41 PM
>To: dba-SQLServer at databaseadvisors.com
>Subject: [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