Grant/Deny table fields permissions

[Mr.Access]

(SQL 2005)

Suppose that I've a db that contains an (Employee table) consistes of the fields :
- ID
- Name
- Code
- Department

and i'd like to show the user say (Kevin) just the name+Department of all employees and hide the ID+Code . I've tried to go to the Employee table properties > Permissions >Select>Columns permissions > Grant the name+Department and Deny ID+Code.

The result >> Kevin can't see any data unless I grant him ALL the fields!!

Any suggestions.

 

[SQL_Hell]

Why not just give Kevin a view that only selects the name and department, then give him permissions to run that view?

 

[Mr.Access]

SELECT denied permission on ....

Hi SQL_Hell ,

Many thanks for the fast reply but you know ..

I've tried your idea but I got all fields appeared when I excuted the View with Kevin account ! and when I made the check-box ( ID and Code) "Deny" , an error msg pop up faced me (SELECT denied permission on ID and Code ).

What can I do ?

Best Regards,

 

[SQL_Hell]

set the permisions to all fields but only show the ones you need in the view, can you post the sql code of your view that you created?

I think you probably get the error because of a possible index on field ID and sql server is using that to return data from the table? can you verify this?

 

[Mr.Access]

It's done ..

Thanks SQL_Hell .