USE TestDB
GO

-- Test Table
CREATE TABLE dbo.Test
(
      ID INT IDENTITY(1,1)
    , VALUE NVARCHAR(50)
)
GO

-- Test Data
INSERT INTO dbo.Test (VALUE)
VALUES
    ('blah'), ('blah'), ('blah'), ('yackity'), ('schmackity')
GO

-- Create Special Schema that will "Own" all the Views
CREATE SCHEMA viewSchema AUTHORIZATION dbo
GO

-- Create a View on top of the Test Table
CREATE VIEW viewSchema.testView
AS
SELECT    ID
        , VALUE
  FROM dbo.Test
GO

-- Create a new DB Role
CREATE ROLE db_viewreader
GO

-- Grant SELECT to all objects in the viewSchema schema to the new DB role
GRANT SELECT ON SCHEMA::viewSchema TO db_viewreader
GO


-- Create a new Test User
USE master
GO

CREATE LOGIN testuser WITH PASSWORD=N'test', DEFAULT_DATABASE=TestDB, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE TestDB
GO
CREATE USER testuser FOR LOGIN testuser
GO
ALTER ROLE db_viewreader ADD MEMBER testuser
GO


-- Execute the following block of code as the testuser account
EXECUTE AS USER = 'testuser'


SELECT *
FROM dbo.Test

SELECT *
FROM viewSchema.testView


-- Revert my security context back to sa
REVERT;

-- Cleanup
ALTER ROLE [db_viewreader] DROP MEMBER [testuser]
DROP ROLE db_viewreader
DROP VIEW viewSchema.testView
DROP SCHEMA viewSchema
DROP TABLE dbo.Test
DROP USER [testuser]
GO
USE [master]
GO
DROP LOGIN testuser
GO

(5 row(s) affected)
Msg 229, Level 14, State 5, Line 58
The SELECT permission was denied on the object 'Test', database 'TestDB', schema 'dbo'.

(5 row(s) affected)