Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Sep 30 '12 at 7:21

This question came from our site for professional and enthusiast programmers.

1  
Typical use case for a view - define a view on top of your table which contains just those columns you want your users to see and then give those users SELECT permission on the view, but deny them the permission on the underlying table –  marc_s Sep 29 '12 at 19:45
    
I offer an alternative view: it may be possible to design a DDL trigger upon the table which propagates any grant/revoke to the views based on it. Those views would be found by inspecting the objects metadata. I'll elaborate and, hopefully, write a complete solution. –  mvaraujo Oct 1 '12 at 4:10

1 Answer 1

up vote 2 down vote accepted

Column level security does not work that way. I'm aware of no mechanism to globally deny access to a certain column for a given user. The GRANT/DENY only works on specific statements like SELECT, UPDATE and so on in combination with a given object.

So in your case, if you removed access on SELECT for column X on table Y the user can still happily execute "select *" views on that table because the view is a different object and is unaffected by this security setting!

The good news is that you can use column permissions on views also. It works just the same as with tables, but you have to set the permission on every view that includes the SSN column.

share|improve this answer
    
That's unfortunate. So is the same true at the table level? So that if I restrict access to the entire table, a view can still access it? If so it's almost better to just put those tables in another database, where I can restrict it to certain users –  user46372 Sep 29 '12 at 16:27
    
@user46372 No you don't need to do that. Read this on Ownership Chains to understand what is happening. –  Martin Smith Sep 29 '12 at 17:00
    
Martin, thanks for the link. So theoretically, if the tables I wanted to lock down were owned by a different user than all the rest of the objects / views, would that force SQL Server to recheck the permissions and prevent access? –  user46372 Sep 29 '12 at 20:27
2  
@user46372 - Yes. That should be correct. But FWIW I agree with marc_s's comnment on the question that it is easiest just to deny access on the table and manage this through creating a view with the desired subset of columns. –  Martin Smith Sep 29 '12 at 21:33
    
Yes, I've heard the views option is the best, but unfortunately our legacy front-end program is an Access Data Project which exposes all of the views and tables (ick). Since we already have a bunch of views created I guess we would we have to go through each one and DENY access? And we would just have to remember to DENY access on every new view we create as well, right? That would be error-prone. –  user46372 Sep 29 '12 at 23:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.