Solved

Data Protection under SQL 2012.  (Column Protection)

Posted on 2016-08-08
6
77 Views
Last Modified: 2016-08-10
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
Comment
Question by:tambrosi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 41747325
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
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41747357
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
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41747361
Or use T-SQL:
DENY SELECT ON TableName (ColumnName) TO UserName

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tambrosi
ID: 41747469
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41748291
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
 

Author Closing Comment

by:tambrosi
ID: 41750187
Thanks for the comments on this.  Appreciated, I will be using a combination of both.
Appreciated!!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Tempdb Contention - SQL SERVER 10 37
section a string 5 53
I am having an ODBC timeout issue with an Access Frontend 6 54
SQL query joining 6 tables in asp.net 4 18
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question