Billy Pang
tuxedo_man at hotmail.com
Tue Jun 29 23:56:50 CDT 2004
oh boy, can't wait to get into this discussion :)...there is one example I can think of.. the example would be based on the physical storage of the sproc in a single db. it is not possible for a sproc to query table in another db that the sproc does not reside in. To illustrate, let's say that there are three copies of the northwind db on db server: northwind0, northwind1 and northwind2; all three have exact db schema; Your goal is to develop a report that counts number of records in the products table in each of the northwind databases. sometimes you may only want to count records in northwind0 and northwind1 but not northwind2; sometimes you may only want northwind2 and northwind1 but not northwind0; and sometimes you want all three northwind dbs in your count. It is possible to create a sp for each of the permutation but if a fourth northwind db is introduced to the system, then the code base is doubled. The alternate solution would be to build dynamic sql to piece together select statement referencing tables using the [db].[owner].[table] format; which tables are pieced together is based on what the user selects as criteria for report. Not elegant solution but it is a lot less code to maintain. With that being said, dynamic sql is not as safe for reporting compared to sproc. I think what Francis is trying to get at is that it is not possible for a developer to claim that a particular "coding method" is never needed. Rather, it is more correct to state that it is not likely that a particular "coding method" is ever needed. Difference between the words "impossible" and "improbable". Similiar to how a judge cannot hand out a verdict to court case before the case is presented no matter obvious it may seem. Another point of reference on this topic would be look up "Validating User Input" in BOL for best practices on this issue. Billy >From: "Arthur Fuller" <artful at rogers.com> >Reply-To: dba-sqlserver at databaseadvisors.com >To: <dba-sqlserver at databaseadvisors.com> >Subject: RE: [dba-SQLServer] Difference between views and queries >Date: Tue, 29 Jun 2004 20:08:54 -0400 > >All I want is an example that cannot be coded by one or more sprocs. > >Arthur > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francis >Harvey >Sent: Tuesday, June 29, 2004 10:47 AM >To: 'dba-sqlserver at databaseadvisors.com' >Subject: RE: [dba-SQLServer] Difference between views and queries > > >Francisco, > >I would like to consider the opinion of someone who shares my namesake >as seriously as possible, but in this case, I believe you suffer from >Arthur's problem of a lack of experience with the very specific problems >I cited. It is fine to talk about not using dynamic SQL in general when >using SQL Server, but just as with normalization, there are exceptions >and no one should be stating it as an absolute rule or bad practice >until they have reviewed the problems I mentioned. Again, I will state, >for those particular problems, dynamic SQL is often the superior >solution. > >Also, the "scaling" issue is teetering awfully close to using FUD. You >can always use any programming method incorrectly, but correctly written >dynamic SQL does not suffer from the horrible efficiency loss that not >having a cached query plan would seem to imply. Similarly, the "time >spent" argument is bad logic. I don't spend less time on my dynamic SQL >solutions, I spend more time because the problems are difficult. Adding >more time for a static SQL solution will not result in a better design >or even a working design in some cases. > >I don't think you, Arthur or I are in disagreement over security, I just >didn't like the oversimplified way security was described, so I chose to >emphasize a particular point. I think this is an important part that >should be highlighted. > >Francis R Harvey III >WB 303, (301)294-3952 >harveyf1 at westat.com > > > > -----Original Message----- > > From: dba-sqlserver-bounces at databaseadvisors.com > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf > > Of Francisco H Tapia > > Sent: Monday, June 28, 2004 12:42 PM > > To: dba-sqlserver at databaseadvisors.com > > Subject: Re: [dba-SQLServer] Difference between views and queries > > > > > > Francis, > > I respectfully agree w/ Arthur on this. Dynamic SQL as a rule, is > > bad design. Many times over you will find that there are comparable > > solutions that often meet the demand much better, and "scale" much > > better than that of dynamic sql. There is no "ONE" right way > > of doing > > things, that is the nature of this business. However > > justifying dynamic > > sql because of some time spent on design is imnsho incorrect. > > You will > > often find more times that enabling users/roles via Views/Sprocs and > > Functions to be far superior solutions than to open wide dynamic sql. > > > > re: security. > > Of course if you deny permissions on any object the take > > precedence over > > allows, this is the nature of security. That is why you must be > > explicitly "SURE" when making DENY requests on any object. > > NOT giving > > rights to an object does not equeal REJECT. > > > > If I do not give a user rights to a view, under Role1, but > > then give him > > rights to that object via Role2. There will be 0 issues w/ > > accessability. However If by poor planning I had a "REJECT" > > rights in > > Role1, then I'd have the issues you are stating. > > > > > > > > > > Francis Harvey wrote On 6/25/2004 2:24 PM: > > > > >Arthur, > > > > > >Do some research on the basic solutions for SQL that has to > > adjust for > > >varying databases, conditions (dynamic search), or servers (different > > > >SQL dialects). Some designs also call for encapsulating > > business logic > > >outside of the data tier. In these situations, dynamic SQL is often > > >the only logical choice. I have only had to deal with dynamic search > > >myself, but this is enough to convince me of its usefulness. > > > > > >Describing permissions solely as additive in nature is > > incorrect. This > > >is the point I was making. > > > > > >Francis R Harvey III > > >WB 303, (301)294-3952 > > >harveyf1 at westat.com ><snip> >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ MSN Premium with Virus Guard and Firewall* from McAfee® Security : 2 months FREE* http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines