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 :
Perhaps the schemas have different owners?
Take a look at this: SQL Server – How to grant SELECT permission on View that access data in schemas from differents owners?
You have to grant usage privilege both of them. Table in SchemaX and view in SchemaY.
If you want to manage permissions by rows, so add a column ‘user’ or ‘role’ then add this condition to where CURRENT_USER = table.user .