[dba-SQLServer] Difference between views and queries

Call me stupid, ignorant, inexperienced, whatever, but so far I have
used "DENY" even once. Maybe that means my clients are victims of bad
design, but so far I have done virtually everything additively, i.e.

Levels:
1. access to a very few sprocs, a few of which do inserts, most of which
permit lookups (for cities, states, products, etc.)
2. ability to update records in some tables
3. ability to create and update records in some tables (only certain
users can add products or change their prices, etc.)
4. ability to run the sprocs underlying various management reports
(grained by department, i.e. warehouse folks don't need to see most of
the reports in the system, just the ones relevant to loading boxcars,
etc.)
5. senior managers, who can view any report.
6. President of the company, who thinks he can do anything but actually
can't :-)
7. Me: I can do ANYthing. Trust me :-)

In some (typically large) firms, Me might not be able to do ANYthing.
For example, as a developer, I might not be able to create a new sproc,
or edit an existing one. I might instead have to request a new sproc
that returns X or edit an existing sproc to also include column Y. In a
situation like this, we have to add (at the least) 

8. DBA: can create, destroy, edit anything

And revise 7 to read:

7. Me: I have access (as developer) to any sproc in the system, but
cannot create, edit or delete any sproc. I must request changes from
DBA.

Maybe it's more efficient to create a new role and then grant certain
accesses while denying others. That approach was simply too hard to get
my mind around. I opted for the strictly additive approach. I could be
wrong, and am prepared to be corrected. The approach I took was to
create as many roles as I needed in order to assemble a strictly
additive structure.

For example, on first pass I let any data-entry person add cities. This
quickly proved foolish. So I created a role that could add data to
lookup tables (simplifying). Anyone in this role can add to/edit lookup
tables such as Cities, States, Countries, etc. Different from this is
the ability to add to/edit tables such as Customers (which most of the
time is a lookup, but fundamentally different than Cities). From there,
it's easy to create a role that can do both.

Ok, I am simple-minded. But it's too complex for me to think of a role
minus certain abilities, because then I have to go in both directions. I
prefer to think of roles strictly in terms of what they can do rather
than what they cannot do. Then I can create a new role and say, "include
data-entry, lookup-table edits, customer-creation and nothing else."
After that, for a new hire I simply assign her to the requisite role.

Maybe that means that I create way too many roles. But so far it seems
like the simplest path to me. I admit that until now I haven't really
given this a lot of thought, but now it occurs to me that there is an
algorithm to solve this.

Let's suppose 100 tables and 10 roles. Each higher role gets access to
all lower roles, i.e. role 1 can access 10 tables, role 2 those same 10
plus 10 new ones, role 3 10 additional tables, etc.

That's a simple case. Complicating it a bit more, suppose role 3a can
access the first 20 tables but only view the next 10. I suppose you
could create 3a by cloning 3 and subtracting the insert/update
privileges for tables 21-30. That would work; it just never occurs to me
to do it that way. Instead I would add the first 20 tables and then add
the next 10, specifying view-only.

Upon consideration, I can't really defend the way I do it, except to say
that conceptually it works for me. The other approach may have
advantages that I'm not seeing, such as performance increase,
architectural simplicity, etc. But so far I don't see them.

I've been wrong before, and this could be yet another occasion of same.

Back to the original subject.... Would someone kindly supply an occasion
in which no sproc can handle the requirement, and dynamic SQL is
necessary? 

I am NOT saying that any single sproc can handle all the requirements; I
am including the possibility that I write one sproc for each situation,
passing various parameters, and that the front end examines the parms
and decides which sproc to invoke.

For example, imagine a form that offers 3 combo-boxes or somesuch, and
tracks in which order I make my selections... One is State/Province, and
one is a DateRange and the third is a CompanyName thing that can handle
wildcards. You fill in the CompanyName thing with "W*", the State thing
with "M*" or "N*" (which I think results in 5 states each", and any
given date range.

I can write one or more sprocs that can handle this input, and it won't
take long. Even granted that the sequence in which you fill in the
controls matters (i.e. sort order), a small number of sprocs can do
this. And if you really want to stretch the point, a single sproc could
receive the sort order and deal with it using CASE statements (though
emphatically that is NOT my chosen approach).

So what I would like someone to offer is a situation in which one or
more sprocs cannot handle the input parameters. Ideally, work it against
Northwind so I can easily check my assertion, and try to come up with a
sproc that can handle your various inputs (the one sproc might invoke
another, depending on conditions; you have to grant me that).

I don't mean to issue this as an in-your-face challenge, but rather to
invite you to teach me this non-obvious lesson.

Arthur