[dba-SQLServer] Difference between views and queries

Billy,
  Typically in a sproc, end users are capable of 
selects/inserts/updates(even deletes) based on the sproc owners rights 
and the sproc's logic.  I relalize that there is a limitation of the 
sproc to run cross joins over databases if said users are not members in 
said db either.

So you'd have to add roles into the respective databases 
(Northwind0/1/2/3 etc..) You'll have to do this anyways for Dynamic Sql 
as you will for Static SQL., but because you are also in pursuit of 
security you'd lock down access to tables and provide access via 
sprocs/views or functions that can be accessed via these roles.  
Something that can be done via dynamic sql however if a developer is 
already using dynamic sql at this point it is unlikely that they will 
consider to lock access to the tables anyways.



Billy Pang wrote On 6/29/2004 9:56 PM:

> 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.
>


-- 
-Francisco