title: 'The SELECT permission was denied on the object ' when working with views on separate schema
body: <p><strong>Note: This is a community wiki entry and is mainly to document a problem together with its solution. I was hardly able to find information on the net to solve this. Hope it helps someone!</strong></p>
  
  <hr>
  
  <p>I have a SQL-Server 2005 DB with the data tables being on the <code>dbo</code> schema. 
  For a new component on the project, I created a new data access layer (using NHibernate) and to nicely capsule this, I created a new schema.</p>
  
  <p>For all the objects I needed, I created a View:</p>
  
  <p><code>myschema.ViewTable1</code>
  <code>myschema.ViewTable2</code>
  etc.
  And granted select permission.</p>
  
  <p>Now, when I tried to access these views by a user that only had select permissions on the views but not the underlying table with NHibernate, I got:</p>
  
  <p><code>The SELECT permission was denied on the object 'dbo.Table1', database 'TestDB', schema 'dbo'.</code></p>
  
  <p>According all the documentation, this should be possible.
  Even more strangely, the SQL did work executed in Management Studio as a plain select.
  But if I executed it with <code>exec sp_executesql</code> as NHibernate does it, it broke with the same exception.</p>
  
  <p>After a long time searching I finally found <a href="https://stackoverflow.com/questions/1661547/how-do-you-set-permissions-on-a-schema-that-has-objects-accessing-other-schemas">this</a> question here on StackOverflow.
  The added <strong>Update</strong> gave the clue:</p>
  
  <p>My new schema <code>myschema</code> was owned by my windows user and not by the <strong>dbo</strong>!</p>
  
  <p>After changing the schema owner to <strong>dbo</strong> and recreation of all the <code>myschema</code> objects (<strong>Yes, this is required!</strong>) things start to work as expected!</p>
  
  <p>Hope this helps someone.</p>