Djabarov, Robert
Robert.Djabarov at usaa.com
Thu Apr 1 14:43:05 CST 2004
...He's consistently missing schema owner...Just an observation ;) 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 2:41 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com