security is not a simple concept. than you add the complexity of database vs application security. some say you need both, others the database is the single point of access and still others say manage it from code.
I prefer to manage security from code, rather than the database (table access and stored procs). In code I will use interceptors to decorate classes. if a user does not have rights to access members of the class I throw an exception. these exceptions can then be caught at the presentation layer and 1. logged 2. redirected to another screen.
As for data security (securing the results of a query) this is a very complex task. The best implementation I have found is Rhino.Security which is part of Ayende's Rhino-Tools. You'll need to understand Castle Windsor and Nhibernate to use Rhino.Security. If you are unfamiliar with these tools, it will not be easy to grasp as it's a paradigm shift in programmatic thinking. Especially from MS's RAD philosophy.
Jason Meckley
Programmer
Specialty Bakers, Inc.