Improve company productivity with a Business Account.Sign Up

x
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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