Note. This is a community wiki and mainly documents the problem along with its solution. I could hardly find information on the net to solve this problem. Hope this helps someone!
I have a SQL-Server 2005 DB with data tables in the schema dbo. For the new component in the project, I created a new level of data access (using NHibernate), and for the beautiful capsule I created a new scheme.
For all the objects I need, I created a View:
myschema.ViewTable1
myschema.ViewTable2
etc. And granted permission to choose.
Now, when I tried to access these views by a user who had only access rights to the views and not a base table with NHibernate, I got:
The SELECT permission was denied on the object 'dbo.Table1', database 'TestDB', schema 'dbo'.
For all documents, this should be possible. In an even stranger way, SQL did the job in Management Studio as a simple choice. But if I executed it with the help exec sp_executesqllike NHibernate does, it broke with the same exception.
After a long search, I finally found this question here on StackOverflow. Added Update gave the key:
My new schema myschemabelonged to my Windows user, not dbo !
After changing the owner of the schema to dbo and re-creating all the objects myschema( Yes, it is necessary! ), Everything starts working as expected!
Hope this helps someone.