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

Question

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.

Answer 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.

Answer 2

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

Answer 3

GRANT SELECT ON [viewname] TO [user]

should do it.

Answer 4

The previous answers are partily correct, you are able to use GRANT statement to only grant permission to a view without granting permission to its base table.

But since it is cross-db, you also need enable Cross Database Ownership Chain at instance level.

You also need to make sure the view and the base table are owned by the same owner or the ownership chain won't work. In modern versions of SQL Server (maybe after SQL Server 2003), all tables and views are owned by its schema by default so it actually means the owner of the schema should also be same. By default, the schema of every user table is "dbo", which means "database owner", so the database owner of the two related DB should be same.

You can read these two articles to know more about SQL Server Ownership Chainingand Cross Database Owernship Chaining. https://www.mssqltips.com/sqlservertip/6394/understanding-sql-server-ownership-chaining/ https://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/

And maybe you also want to know more about SQL Server logins and database users, since for the user who you want to grant access to the view only in DB A, you do need to give him at least "public" role in the DB which your base table resides: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver15