March 21, 2012 at 2:01 pm
How can I deny users added under db_datareader role from viewing a specific column of a table?
March 21, 2012 at 2:07 pm
You could create a view that does not include the column you want to hide. Grant select to the view to the users role. Then deny select to the base table to that users role.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2012 at 2:09 pm
deny trumps any other grants unless the user is a sysadmin...so deny it
DENY SELECT ON [dbo].[PAYROLL] ([RATEOFPAY]) TO [ClarkKent]
Lowell
March 21, 2012 at 2:29 pm
I'm little confused here because if I create a custom db role, I'm able to add an object under Securables but for db_datareader and db_datawriter, I'm not seeing Securables.
I was thinking to add the table under Securables of datareader and datawriter roles and deny all the users from viewing that specific column of a table.
March 21, 2012 at 2:34 pm
Seans idea of using a view has a lot of advantages.
if you deny SELECT on a column like my first example,
if you have code in an application they are using that does SELECT * FROM SOMETABLE, they will get hit with an error announcing the columns has been denied select...so they cannot use SELECT * on the table any more...and all columns would have to be explicitly named.
that might adversly affect a lot of code.
Lowell
March 21, 2012 at 2:36 pm
Didn't think about that Lowell. Maybe even rename the original base table so the new view can have the name of the base table currently so that no code has to change???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2012 at 3:18 pm
Ours is a datawarehousing environment. We always advice the users/teams not to do SELECT * unless needed.
We are going to let all the teams know in advance what we are going to do with that column. So for unapproved users, SELECT * won't work. This has been discussed in the meetings that all unapproved users will have to modify their reports.
There should be a good reason which I'm not aware of why we don't use views. So creating a view isn't an option for me.
March 21, 2012 at 3:24 pm
Well that would certainly point out the queries that use select * then. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply