We don't have an official answer from MS yet, but it is looking like a bug. And I just figured out a big key to it: Common Table Expressions.

Let me update the scenario:

* schema1.ViewA ... selects from schema2.ViewB ... using a CTE

* schema2.ViewB ... selects from dbo.ScalarFunctionC ... using a CTE (and the function call is inside that CTE)

* dbo.ScalarFunctionC does not, itself, reference any objects at all, just internal calculations

My (and your) rudimentary tests yesterday didn't include CTEs. I just typed out a new simple test today, but made sure both views also have CTEs, and the "middle" one calls the function. And I get the same error when a user that only has permissions on schema1.ViewA tries to call it - access denied on the function.

CREATE DATABASE TestDB;

GO

IF SUSER_ID('user1') IS NULL

CREATE LOGIN user1 WITH PASSWORD = 'abc', CHECK_POLICY = OFF;

USE TestDB;

GO

CREATE SCHEMA schema1 AUTHORIZATION dbo;

GO

CREATE SCHEMA schema2 AUTHORIZATION dbo;

GO

CREATE USER user1 FOR LOGIN user1 WITH DEFAULT_SCHEMA = schema1;

GO

CREATE TABLE dbo.things (id int);

INSERT INTO dbo.things (id) SELECT 1;

GO

CREATE FUNCTION dbo.f_thing (@x int) RETURNS INT AS

BEGIN

RETURN 1;

END

GO

CREATE VIEW schema2.vGetThings AS

WITH CTE AS (SELECT id, dbo.f_thing(1) as f_thing FROM dbo.things) SELECT id, f_thing FROM CTE;

GO

CREATE VIEW schema1.vGetAllThings AS

WITH CTE AS (SELECT id, f_thing as the_thing FROM schema2.vGetThings) SELECT id, the_thing FROM CTE;

GO

GRANT SELECT ON SCHEMA::schema1 TO user1;

EXECUTE AS USER = 'user1';

PRINT 'User: ' + USER_NAME();

SELECT * FROM schema1.vGetAllThings;

REVERT;

/* cleanup

USE master;

DROP DATABASE TestDB;

DROP LOGIN user1;

*/

Result:

(1 row(s) affected)

User: user1

Msg 229, Level 14, State 5, Line 6

The EXECUTE permission was denied on the object 'f_thing', database 'TestDB', schema 'dbo'.

The exact same code, on 2008 (and just for fun I tried it on 2005 as well)... works as expected; user1 can read from the view without any error (though can't call the sub-objects directly).

Edit:

This goes just a tad deeper than the CTE's. The view using them are fine - so long as the CTE doesn't call the function. In fact, if the function call is still in the view (schema2.vGetThings), but not inside the CTE, just in the SELECT from it... it works fine.