How can I achieve row level security in SQL Server

Question

Is there any way to get row-level security in SQL Server 2014?

My problem is:

• I have a table with data for multiple regions
• I created a view for each region
• Specific user will have access to specific region views
• But without giving access to the underlying table, those users are unable to access the views

I need to restrict users to view only certain rows. Are there any possibilities to do so?

Answer 1

The same SQL query returns results based on identity. No special database code required. You can control how the rows and columns return, and even aggregation. For example, the SQL below will return different results for managers, analysts, and developers. select * from employee_salaries;

Answer 2

I have achieved it by creating views for the specified rows and giving permission to only views not underlying table

• so user has visible to only rows which are returned by views. We can control rows to be returned by where clause in view.
• but table may contain other rows as well

key terms : Ownership chaining