Record Level Security in SQL Server

Question

How would you go about implementing record level security in SQL Server?

EDIT:

I've a requirement to restrict data based on user's privileges. Lets say I've a table of Contacts. I should be able to restrict the set of data that a user can see. Hope this helps :-)

Answer 1

You would have to create your own tables and join to those to get security. For example, in a table with just UserID, RecordID, and a Read and Write column, you'd do this:

select
    a.*
from
    mytable a
    inner join permissions b on
        a.recordid = b.recordid
where
    b.userid = myuserid
    and b.read = 1

Analysis Services (which comes free with SQL Server Standard or higher) will give you record level security. It also gives you the necessity of learning OLAP and multidimensional databases, so that could be a drawback depending on your needs/wants.

Answer 2

Here's an older article on Implementing row level security in SQL Server databases which may provide some info.