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