While SQL Server controls access to tables and objects on a user-by-user basis; restricting a user to see only certain rows in table is a common need.
You might set permissions to allow users to see Orders in a e-commerce system, but you will want to prevent them from seeing orders by other users. If your database held more than one companies orders, you would want to prevent companies from seeing each others orders.
Typically your application layer would implement this requirement of restricting the data a particular user could see. But wouldn’t it be nicer if it was done in the database layer so users had (potentially) better access to data?
Microsoft has published a nice article on Implementing Row and Cell Level Security in Classified Databases Using SQL Server 2005. This is a really in depth article with code and examples covering the implementation of row and cell based security for groups of users, including encryption.
Reminds me of how SQL Server 2005 itself now prevents users from seeing objects they do not have permission to in the system tables. Eg: select * from master.sys.databases will only show you the databases you have access to, NOT necessarily all the databases on the server. The same goes for all the other system views in SQL Server 2005.