Suppose I have the following table (in schema 'SchemaX') :
CREATE TABLE [SchemaX].[myTable]...
And the following view, build upon the previous table but on a different schema ('SchemaY) :
CREATE VIEW [SchemaY].[myView] AS SELECT * FROM [SchemaX].[myTable] WHERE ...
I give permission to a certain user to read the view :
GRANT SELECT ON OBJECT::[SchemaY].[myView] TO [myUser]
But he is still not able to read it and gets the following error :
The SELECT permission was denied on the object 'myView', schema 'SchemaY'
If I put the view in the same schema as the table, the query will work.
Can you explain why it is working when both are on the same schema and why it is not when they are on separate schemas?
EDIT: Turns out my schemas had different owners.
Found this article that explains why it doesn't work when schemas owners are not the same : https://www.itprotoday.com/sql-server/security-through-ownership-chains