Solved

SQL 2005 Column-Level Security

Posted on 2009-05-11
10
652 Views
Last Modified: 2012-05-06
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
Comment
Question by:Bill Warren
[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
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24357881
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 24357893
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
 
LVL 9

Expert Comment

by:ezraa
ID: 24357998
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:Bill Warren
ID: 24358476
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
 
LVL 9

Accepted Solution

by:
ezraa earned 400 total points
ID: 24358803
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
 

Author Comment

by:Bill Warren
ID: 24358892
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
 

Author Comment

by:Bill Warren
ID: 24358905
Oh okay i think I found it under properties
0
 
LVL 9

Expert Comment

by:ezraa
ID: 24358913
Right click and click on Properties

Then in the window that opens you should see permission on the left side.
0
 
LVL 9

Assisted Solution

by:ezraa
ezraa earned 400 total points
ID: 24358964
Your looking for this:
perms.jpg
0
 

Author Closing Comment

by:Bill Warren
ID: 31580278
Thanks a bunch everyone
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

624 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