SQL Server 2008 EF 4 - limiting database records returned using permissions?

Question

In our database all tables are linked back to a single table. This table has a bit column to limit whether the record is displayed. Currently the records are filtered on the code side of the website.

Is there any way to set up permission so that userA would never see any record in the database where that common bit column was set to true?

We are using SQL Server 2008.

Alternatively we are also using entity framework 4.0 in .net 4 (in c#) if you have any ideas how it might be accomplished there?

Thanks for your feedback.

Answer 1

According to this excellent TechNet article, row level security is indeed possible with SQL server, if you structure your database accordingly, using views or stored procedures to limit the result by the current user. To my knowledge SQL Server does not have built in functionality for row level locking.

Answer 2

You could define a view on top of your table, which only shows those rows where that "active" column is set to 1 (true).

You could then revoke the SELECT permission on the underlying table from that group of users, but in turn grant them SELECT on that new view you've created.