table/view permissions question

[jsteph]

Hi all,
I'm having a mental block here. I just want to create a view on some tables, and let a lower-credentialed user see this limited set of data. I created it in em, then granted Select permission to the new user. I try to link the view via odbc, and it lets me link it, but when I open it, it says that user doesn't have SElect permission on the tables in the view. I thought that was the whole purpos of a view (or one of the purposes)--to allow selected fields of filtered data in tables to users which wouldn't normally be able to see those tables. What am I doing wrong here in the creation of the view?
Thanks,

 

[SQLSister]

Only thing I can think of is that maybe they are specifically denied permission on the base table rather than having no permission to select from it. I believe a denial of permission trumps everything else.

Questions about posting. See faq183-874

 

[nice95gle]

You need to GRANT SELECT permission on the columns that you want the user to see:

GRANT SELECT ON field1, field2 field3 TO USER

Then put those fields in your view and you should be fine.

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

sorry forgot the tablename

GRANT SELECT ON YOURTABLENAME(field1,field2, field3) TO USER

Well Done is better than well said
- Ben Franklin

 

[jsteph]

Thanks, I ended up doing that, but I guess what I was hoping for was something like a 'WITH OWNERACCESS OPTION' clause, as Access' Jet sql has.

I wanted to be able to create a view which gives proxy permission to the tables/fields in the view to those with permissions on the view, because it's not so much the fields, but the rows--I'm trying build a bit of row-level security into this particular application.
--Jim

 

[SQLSister]

This can be done, but you need to read in BOL about ownership chains.

Questions about posting. See faq183-874