I have a database on SQL Server 2008 R2 that has two schemas, the default dbo and a new one called Test.
I have a view in the Test schema called View1 that selects from dbo.Table1. Both schemas are owned by dbo.
I have a user called User2 that has SELECT permission to the Test schema.
User2 can login and see View1 but when it tries to select from it this error comes up:
The SELECT permission was denied on the object 'Table1', database 'Database', schema 'dbo'.
I thought since everything was owned by the same owner, ownership chaining would check the permission at entry point and allow the query to execute.
For kicks, I setup a similar example under a SQL Server 2012 Express database and it works fine.
Any ideas why this doesn't work?
Best Answer
Ownership chains work down not up. If you give the user rights to the view, they can query the view without needing rights to the objects under the view, in this case the table. It doesn't work the other direction. The user always needs rights to the object they are attempting to access. All the objects under that object will come automatically.