Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

SQL 2005 Column-Level Security

How can I apply column level security on my tables? I understand that is easily done on a view but I cannot see how to do it on the tables.
0
Bill Warren
Asked:
Bill Warren
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can also grant column-specific permissions per column in sql server.

listing: sp_column_privileges : RTFM:
http://msdn.microsoft.com/en-us/library/ms187765.aspx

the link has also in "see also" the links to GRANT and REVOKE

it's also possible to do it in the GUI ...
0
 
ezraaCommented:
Use the GRANT, REVOKE, and DENY statements:

example - don't allow username to select the [MyColumn] column from the table [MyTable]:

revoke select ([MyColumn]) on [MyTable] to username

Open in new window

0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Bill WarrenIT ManagerAuthor Commented:
How is it done in the GUI You ean in the Management Studio? Also if not in the GUI... where do I go to apply the Grant, Revoke, and Deny statements?
0
 
ezraaCommented:
right click on the table and choose properties -> Permissions

on the bottom you should see the button "Column Permissions"

You need to click (or add) a role, then you can select a permission and select the columns that has (or is revoked) that permission.
0
 
Bill WarrenIT ManagerAuthor Commented:
When I right click on a table I don't have permissions see the screenshot. The version is MS SQL 2005 Standard SP2
screen.bmp
0
 
Bill WarrenIT ManagerAuthor Commented:
Oh okay i think I found it under properties
0
 
ezraaCommented:
Right click and click on Properties

Then in the window that opens you should see permission on the left side.
0
 
ezraaCommented:
Your looking for this:
perms.jpg
0
 
Bill WarrenIT ManagerAuthor Commented:
Thanks a bunch everyone
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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