• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

Data Protection under SQL 2012. (Column Protection)

Been looking for the best way to protect a column of data IE ssn, salary within a table.  
I need to protect the ssn and salary field from people.   These folks will have access to SQL server Management tool. Is there a way to add security by users to column using sql 2012.  
Any other ideas that would work?
I looked at encryption, but the app that uses the data cannot handle the decrypt.  

Thanks
Terry
0
tambrosi
Asked:
tambrosi
  • 3
  • 2
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, how about creating a view that contains all columns except for the confidential ones, and then give users access to the view, and remove access to the table?
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can always DENY select on the columns that you want for the respective users (In SSMS right-click on the desired table name and select Properties and go to Permissions option).
DenyColumnSelect.PNG
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Or use T-SQL:
DENY SELECT ON TableName (ColumnName) TO UserName

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
tambrosiAuthor Commented:
Thanks for the info.   When I am under SSMS, and look at the 'Column Permissions" tab, it is greyed out.   Is there
special security that is required to see that tab to display the fields on a table.
Thanks
Terry
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
When I am under SSMS, and look at the 'Column Permissions" tab, it is greyed out.   Is there
 special security that is required to see that tab to display the fields on a table.
You need to select an user or role first. After that the option will be enabled.
0
 
tambrosiAuthor Commented:
Thanks for the comments on this.  Appreciated, I will be using a combination of both.
Appreciated!!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now