SQL Server supports field-level database security, which you can set by user or by SQL role. You can grant these permissions on a table level, but more commonly, views are used.

However, I am not sure that this will be the best approach for your app, for one rather important reason: I prefer to use one SQL Server username for my apps, to make the most of ADO connection pooling.

What GUI and middle layer languages are you using? My guess is that dot-net has its own way of dealing with this stuff, but I am so new to it that I'd rather someone else comment. If you are using "classic" asp and/or vb (or even vfp), there are a few options that I'd recommend researching.

(1) is this an in-house app and can you get by with an IE only solution? If so, you could use COM+ security and COM+ roles to call slightly different database queries or stored procedures. Or, you can call a common stored procedure or SELECT statement but run through the dataset on the middle layer and change/hide data as necessary before returning the set to the front-end.
(2) keep your current approach, with a database tracking the user rights, and in each middle layer, hit the database a second time to get this info and take the appropriate actions. I don't like this because it requires a second database hit, but then again, your user rights table will be quite small, relatively speaking.
(3) keep your, current approach, with a database tracking the user rights, and wrap all your data access in stored procedures. Then, in the stored procedures, hit the tables as necessary to hide/de-select data. This removes the additional database hit, but it would require that all your data access be wrapped in sprocs (I prefer to do this anyway, but many folks do not... and before you lock yourself into the approach you should makie sure it's what you want.)

Hope this helps, as a starting point at least.

>My UI is now responsible for field level security. The groups are stored in a dbf file. I am converting my backend to SQL. What is the best way to implement security in SQL. I was hoping to create 1 group for my application to access the data but then how would I implement the field level security? Does SQL support field level and can I use my application group login for this or should I just let my database still control field level security?
>
>Thanks