I think I need some pointers on security in SQL Server. I'm trying to restrict some of our end users from getting access to certain columns (i.e. SSN) on a table.
I thought I could just use column level security to restrict access to the columns. That successfully prevented users from accessing the table directly, but I was surprised that they could still get to those columns through a view that accessed that table.
I followed the tips here: http://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/ Those were very helpful, but when I created a view at the end, the intern was able to access that column by default
I've read that views are the best way to accomplish this, but I really don't want to go through and change all of the views and the legacy front-end application. I would rather just restrict it once on the table and if a view tries to access that column it would just fail.
Is that possible or am I misunderstanding how security works in SQL Server?
SELECTpermission on the view, but deny them the permission on the underlying table