One database with two schemas: cross-schema View permissions issue?

Question

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?

Answer 1

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.

Answer 2

The following is a script of the scenario you describe. It successfully performs a SELECT from the view in the Test schema.

Please post any divergence between your database implementation (including logins) and the following implementation, observing (a) SQLCMD mode in SSMS, (b) replace 'myInstance' with your SQL Server instance, and (c) replace database 'Checkerbox' with your database name:

USE [Checkerbox]
GO
CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [User2] FOR LOGIN [User2] WITH DEFAULT_SCHEMA=[dbo]
GO
CREATE SCHEMA [Test] AUTHORIZATION [dbo]
GO
CREATE TABLE dbo.Stock
(
Id int NOT NULL IDENTITY (1, 1),
Acronym char(3) NOT NULL,
Name varchar(50) NOT NULL,
CONSTRAINT [PK_Stock] PRIMARY KEY (
Acronym ASC
)
)  ON [PRIMARY]
GO
CREATE VIEW Test.vw_Stock
AS
SELECT Id, Acronym, [Name]
FROM dbo.Stock
GO
GRANT SELECT ON SCHEMA::[Test] TO [User2]
GO

:CONNECT myInstance -U User2 -P pwdUser2

USE [Checkerbox]
GO

SELECT * FROM Test.vw_Stock