Glen,

A thought -- If you have the the back-end filter the records returned, you'll be limited to Full or No access. If you need read-only level access, you'll still need something on the client. Might not be a problem for your situation.

Dan

>To All.
>We are developing a new product with either SQL Server or MSDE as a backend, depending on the size of the purchaser. Our older product was in VFP. There are 60+ tables but the main model can be traced back to one parent. I need to implement a security scheme where users are filtered out of some records, but not others, depending on what access they are given. They are also allowed to select from several different groups of access. This is a horizontal security scheme based against the main parent.
>
>A single select statement with 2 inner joins can manage the filter. Now my idea is to use Views. When the user logs in there would be narrow view created for him based on what he has access to. There would only be id's for the main parent records to which they have access, and a few fields for lookup purposes. No modification via the views, only against the base tables. I think this achieves 2 big goals.
> 1) It keeps the security/filtering on the back end, instead of the client.
> 2) It keeps the formula in one location. That would be the stored procedure which creates the view.
>
>Any lookup or query against the child tables could use a simple join against the view for filtering & validation as to what the user can see and access. The security formula, with the correct multiple inner joins, would not have to be built into any of these queries.
>
>Drawbacks I see are:
> 1) Creating a view at run time for each user logged in. 30 users adding 30 views seems like it can create a large overhead. 40 users, 50... You get the idea.
> 2) I'm not sure about speed issues or refresh issues.
>
>
>What I need to know is if I am on the right track. Are there some issues I need to be aware of? Any better ideas? I want to test this against some large data sets this week. What I am reading seems to tell me this is the way to go. I just don't have real experience to draw on.
>
>Any feedback, short of telling me what a numbskull I am, is appreciated.
>
>Regards,