I have a database on SQL Server 2008 R2 that has two schemas, the default dbo and another one called Webapps, and both schemas are owned by dbo.
I created a view in the Webapps schema (Webapps.getInventory) that just does a simple select from dbo.Inventory. Then I created a user called Webuser that has SELECT permission to the Webapps schema.
Webuser can login and see the Webapps.getInventory view, but when it tries to select from it this error comes up:
The SELECT permission was denied on the object 'Inventory', database 'Database', schema 'dbo'.
I was thinking that since both schemas are owned by dbo, ownership chaining would allow the query to execute. The only way I seem to be able to get it to work is give Webuser permissions on dbo, or add it to the db_datareader role for the whole database. To me that seems to defeat the purpose of trying to separate things out into the Webapps schema if the user has to have read ability to everything else in the database.
So, am I overlooking something in my setup? Or is it correct that Webuser must have permission on both schemas in order for this view to work?
Eric I think you are "jumping" over a step in the ownership chain... Have (another) look at this article: Ownership Chains Here is a snippet "Ownership chaining enables managing access to multiple objects, such as multiple tables, by setting permissions on one object, such as a view."
The chaining doesn't care that both schemas are owned by dbo, but rather that each login/user has appropriate permissions on object. So if you have a table owned by dbo that is included in a view where you have given select access to the webapps schema, then the users with access should have access to the table. The ownership chain isn't checked deeper than the fact that they can select against the view, no need to evaluate if they can select against the table.
So long story short, if you wrap the dbo.table in a view where webapps has select permissions then you should be good.
Hope that helps...