Grant Select on a view not base table when base table is in a different database

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? I would rather not do it the latter way.

Solution 1:

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.

Solution 2:

GRANT SELECT ON [viewname] TO [user]

should do it.

Solution 3:

I also had this problem. I used information from link, mentioned above, and found quick solution. If you have different schema, lets say test, and create user utest, owner of schema test and among views in schema test you have view vTestView, based on tables from schema dbo, while selecting from it you'll get error mentioned above - no access to base objects. It was enough for me to execute statement

ALTER AUTHORIZATION ON test.vTestView TO dbo;

which means that I change an ownership of vTextView from schema it belongs to (test) to database user dbo, owner of schema dbo. After that without any other permissions required user utest will be able to access data from test.vTestView