How does security context propagate between schemas within a database? We have a SQL Server 2008 database that contains two schema's.  The owners of these schema's have been granted full SELECT permissions to all tables within each others ownership.

I have created a user ID and have granted it access to only SQL Views created in both of these schema's but not the underlying tables.  In the example below I've created two views, one in each schema, that select data from one table in one of the schema's.

If I login as the schema owner (either testdata or testcntrl) it works regardless of the schema I execute the view from.

If I execute the View as my new user in the schema that the same table exists in (in the example testdata) the select is successful, but if I try from the other schema (testctrl) the select fails giving me the following permission error: The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'..  

How does security propagate between schema's within a single database?  Why is this successful from one schema but not the other?

Here is a rough example of what I am trying to do, please let me know if I can explain this better, as for Im not remotely close to a dba.

USE [DEV_DATABASE]

-- Create tables and insert dummy data
CREATE TABLE [DEV_DATABASE].[TESTDATA].[UserData]
    (
        COL1 CHAR(10),
        COL2 CHAR(10),
        COL3 CHAR(10),
        COL4 CHAR(10),
        COL5 CHAR(10)
    );

 INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('1','2','3','4',5');
 INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('11','22','33','44',55');


CREATE TABLE [DEV_DATABASE].[TESTCTRL].[ControlData]
 (
        CTRL1 CHAR(10),
        CTRL2 CHAR(10),
        CTRL3 CHAR(10),
        CTRL4 CHAR(10),
        CTRL5 CHAR(10)
    );

 INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('a','b','c','d',e');
 INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('aa','bb','cc','dd',ee');


-- Create views
CREATE VIEW [TESTDATA].[DATA_TEST_VIEW] AS 
SELECT 
 COL1, COL2, COL3
FROM  [TESTDATA].[UserData]

CREATE VIEW [TESTCTRL].[CTRL_TEST_VIEW] AS 
SELECT 
 COL1, COL2, COL3
FROM  [TESTDATA].[UserData]  


-- permissions
DENY SELECT ON [TESTDATA].[UserData] to JOEUSER;
GRANT SELECT ON [TESTDATA].[DATA_TEST_VIEW] to JOEUSER;    
GRANT SELECT ON [TESTCRL].[CTRL_TEST_VIEW]  to JOEUSER;

EXECUTE AS USER = 'JOEUSER';
-- tests
SELECT * FROM [TESTDATA].[UserData];
-- fails as expected

SELECT * FROM  [TESTDATA].[DATA_TEST_VIEW];
-- passes as expected

SELECT * FROM  [TESTCRL].[CTRL_TEST_VIEW];
-- fails, and should pass?

REVERT;


Error message received:

SELECT * FROM  [TESTCRL].[CTRL_TEST_VIEW];
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'.
Thanks to the OP's clarification of the error, I'm going to post my comment as a suggested answer.

In SQL Server, permissions are checked only when ownership changes.

Consider the following tables:


Table SchemaA.Table1, owned by Alice.
View SchemaA.View1, owned by Alice, depends on Schema1.Table1.
View SchemaB.View1, owned by Bob, depends on Schema1.View1.


Consider these situations:


Alice attempts to select from SchemaA.Table1: 

No change in ownership so permissions are not checked on the table. Alice is allowed access.
Alice attempts to select from SchemaA.View1: 

No change in ownership so permissions are not checked on the view or on the table. Alice is allowed access.
Bob attempts to select from SchemaA.Table1: 

There is a change in ownership on SchemaA.Table1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.Table1.
Bob attempts to select from SchemaA.View1: 

This is a change in ownership on SchemaA.View1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.
Bob attempts to select from SchemaB.View1. 

There is no change in ownership on SchemaB.View1 (Bob is attempting an operation on Bob's object) so permissions are not checked on SchemaB.View1. There is a change in ownership from SchemaB.View1 to SchemaA.View1 so Bob's permissions (not Alice's) are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.


So, in the OP's sitation, the view [TESTCTRL].[CTRL_TEST_VIEW] depends upon the table [TESTDATA].[UserData]. The view and the table have different owners so there is a change in ownership (something some SQL Server books call a "break in the ownership chain"). To query the view, JoeUser needs to be granted the appropriate permissions to both the view and the table.