MS Sql Server table based user authorization

Question

Hi there,

I want to restrict some MS Sql Server users. For example, one user should just be able to see 2 columns of "Customers" table and none other. And this user shouldn't be able to create any manipulation queries. But one user should be able to do everything on all tables and all columns. How do i do that?

BR,

Çağın

Answer 1

Create a view that only selects the two columns in question. Then remove the select, update and insert grants from the original table, and only grant the user select on the new view.

Answer 2

Use the datareaders role to restrict write access, and use a view to hide columns. Remove the the select right on the table to prevent a query directly on it.

Answer 3

I find a way like this :

• Click securables tab from user name on database's security.
• Click search button and choose object type. (I need just tables)
• Click Browse and choose table(s) and click OK
• Choose Explicit and click Column Permissions
• Choose Columns and click OK

it is working for my situation.