Permission to open view not table

[StoneDeCroze]

Hi All,

Is it possible to set up permissions so a user can open and see data in a view, but not open and see data in the underlying tables?

I have tried denying access to the tables and granting to the views, but this doesn't work. The overall effect is denial.

If it can be done, can someone please show me how.

Regards
Peter

 

[nigelrivett]

Denying access to the table will override the view permissions I think.
If you don't give permission on the table to the user (as opposed to denying access) but give permission on the view and the owner of the view is the same as the owner of the table then it should work.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

After a quick test - denying permission doesn't seem to override the view permissions.

Is your view owned by the same user as the table? Otherwise permissions will be check on the table as well.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[StoneDeCroze]

We used to run SQL7. The tables were in one DB and there were views in another.

I didn't grant any access to the DB with the tables in it and all was well with the world (and my security).

We have moved to SQL2000 and if I employ the same scenario -the views no longer work.

For the views to work I have to grant DB and SELECT access to tables, this effectively gives the users direct access to the tables - which I don't want. I don't want them to know of the existance of the DB at all.

 

[StoneDeCroze]

Hi Nigel,

Thanks for your help - it did start me thinking. Anyway I have now found the problem or should I say "Microsoft design feature".

http://support.microsoft.com/default.aspx?scid=kb;en-us;810474

My tag line is now:
MS Patches and Service packs - they do more than fix bugs.

 

[nigelrivett]

If the objects are in different databases then you will need permission on the tables.
The only way round this is to enable cross database ownership chaining.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Which I guess after your last comment is what that link points to .

Be careful about it - new feature so needs testing.

I'm a bit surprised it worked with v7. Thought permissions had always been checked across databases.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.