How to give users access only to certain columns of certain tables in a database?

Question

Post reply

How to give users access only to certain columns of certain tables in a database?

paul.terek

Old Hand

Points: 320
More actions
July 30, 2013 at 2:19 pm

#297022

Hello,
I am new to SQL Server 2008 administration and I have been asked to give a user access to only 1 table of a database. In addition i have been asked to give them access to only 2 columns within that table. How can I do this? I was told there is more than one way if you know multiple ways please share. If I have to type a command where do I type it? please start from scratch with me. Thank you very much I appreciate your input.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

vultar Hall of Fame Points: 3271 More actions · Answer 1

Have a look at creating a view or a stored procedure.

Cheers

Vultar

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You need to create a view (reference) and then grant permissions only on that view (reference)

EDIT: Corrected language for the links.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

paul.terek Old Hand Points: 320 More actions · Answer 3

Is there a way to do it in SQL Server Management Studio (SSMS)?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I suppose that you mean using a graphic interface.

You can right click on the views folder and choose the option New View..., that will give you a designer.

For the permissions, you need to go to the Users folder inside of the Security folder and right click on the user and go to Properties and choose the permissions in the Securables page.

I hope you can find your way with this.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

timwell SSCertifiable Points: 5315 More actions · Answer 5

paul.terek (7/31/2013)
Is there a way to do it in SQL Server Management Studio (SSMS)?

In the object explorer find the database you are working with, right-click and select New Query to get to the place where you type in the commands.

Also, if you expand the "folders" under the database you can right-click on Views and select New View... which is more of a wizard/drag & drop/visual kind of process.

The permissions will be through the Security options, either under the DB or on the server level, users, roles, etc.

Also in the links to the Spanish documentation given above, I was able to replace "es-es" with "en-us" to get the article in US English....

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

timwell (7/31/2013)
Also in the links to the Spanish documentation given above, I was able to replace "es-es" with "en-us" to get the article in US English....

I hadn't noticed that the links were for the spanish articles. I'm sorry but that's the default option for my browser.:blush:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

paul.terek Old Hand Points: 320 More actions · Answer 7

If I right click on a table and select properties then permissions.

I can add users or roles and underneath I can set permissions for the users.

Alter, Control, Delete, Insert, References, Select, Take ownership, Update, View change tracking, View definition. Then I can select Grant, With Grant or Deny.

There is also a button for Column Permissions...

Can I use these options to give a user acces to only 2 columsn within a table? If so which settings do I need to make.

timwell SSCertifiable Points: 5315 More actions · Answer 8

I think the idea was to create a view which is basically a select of the 2 columns you want from the table, then you can look at the properties of the view to assign users to that.