Grant Select on a view not base table

Question

This one is on "Experts Exchange". Let's open the answer up to the rest of the world.

I have a view which is selecting rows from a table in a different database. I'd like to grant select access to the view, but not direct access to the base table. The view has a where clause restricting the number of rows.

Can I grant select to the view and not the base table, or do I need to switch to a stored procedure (not preferred at all).

Thanks!

Answer 1

GRANT SELECT ON [viewname] TO [user]

should do it.

Answer 2

You can grant permissions on a view and not the base table. This is one of the reasons people like using views.

Have a look here: GRANT Object Permissions (Transact-SQL)

Answer 3

As you state in one of your comments that the table in question is in a different database, then ownership chaining applies. I suspect there is a break in the chain somewhere - check that link for full details.

Answer 4

I tried this in one of my databases.

To get it to work, the user had to be added to the database housing the actual data. No rights were needed, just access.

Have you considered keeping the view in the database it references? Re usability and all if its benefits could follow.

Answer 5

Question Removed. I figured out the answer.