[dba-SQLServer] Difference between views and queries

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


> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com 
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of Arthur Fuller
> Sent: Friday, June 25, 2004 4:23 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer] Difference between views and queries
> 
> 
> 1. I haven't yet seen a case where dynamic SQL is necessary. All it
> takes to avoid it is one or more well-constructed sprocs, IMO.
> 
> 2. This is a good thing. But the point I was making is that 
> you set up a
> hierarchy of permissions by role, and then add roles to roles. This
> works very well.
> 
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of Francis
> Harvey
> Sent: Thursday, June 24, 2004 4:28 PM
> To: 'dba-sqlserver at databaseadvisors.com'
> Subject: RE: [dba-SQLServer] Difference between views and queries
> 
> 
> Arthur,
> 
> Two points:
> 
> 1) Never say no exceptions. Currently, I need dynamic SQL,
> and there is no comparable work around without it.
> 
> 2) Permissions are not additive in that denied permissions always take
> precedence. You cannot gain rights to an object by adding adding
> additional roles if you have been denied rights at some level.
> 
> 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 Arthur Fuller
> > Sent: Thursday, June 24, 2004 4:05 PM
> > To: dba-sqlserver at databaseadvisors.com
> > Subject: RE: [dba-SQLServer] Difference between views and queries
> > 
> <snip>
> > NO users except sa (and possibly developers) should have 
> access to any
> 
> > SQL table. Everything should be done with views or sprocs 
> or UDFs. No 
> > exceptions.
> > 
> > Access to said objects should be governed by roles, and users
> > should be
> > assigned to roles; this can be done additively. I.e. suppose 
> > you have 3
> > levels of access, a, b and c. Everyone in level B can do 
> > everything that
> > everyone in level A can. So just role B as a user in level 
> A; then you
> > "inherit" everything permitted for level A. Similarly, add 
> role C as a
> > user in level B, and inherit both B and A. This is a 
> > simplistic example;
> > it may arise in the real world that level C should be able to do
> > anything A can but nothing that B can. In that case it's a 
> little more
> > difficult, but the underlying principle is the same. IMO, as 
> > always, and
> > I could be wrong, and it wouldn't be the first time.
> > 
> > Arthur
> <snip>
<snip>