[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>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com