0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
vignesh
  • 1,414
  • 5
  • 19
  • 38
  • Please Read SQL Server Row level security guidelines and try to make your question more specific: https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017 – Pop Catalin May 30 '19 at 07:59
  • 1
    Row-Level Security was added in SQL Server 2016. If you need to implement something similar, you'll need to build something bespoke to you; perhaps by forcing users to use SP's to access data and revoking their `SELECT`, `UPDATE`, `DELETE`, etc permissions. If you can't do that, and you must have row-level security then I would suggest upgrading. Not a "cheap" option (depending on the licences and volume you need), but if it's a requirement that you have that functionality and you can't provide it a different way then it's up the business to decide what they want to do. – Thom A May 30 '19 at 08:04

2 Answers2

0

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

vignesh
  • 1,414
  • 5
  • 19
  • 38
-7

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;

djafar ould slimane
  • 1
  • 1
  • How does `select * from employee_salaries;`, on its own, return different results for different people? That would return **every** row and **every** column from the table `employee_salaries`. Nothing in that statement stops a row/column being returned. Without elaborating on why that statement would produce that behaviour this answer will only attract (further) downvotes. – Thom A May 30 '19 at 08:10