You'll need to move the views to a different schema to utilize this security tom foolery.
Basically you can GRANT SELECT permissions on a Schema, but you are unable to limit the SELECT privileges to views only.
The workaround, as you identified, is to create a schema that only owns the views in question. This schema is also owned by the same principal that owns the base schema where the tables reside so your permissions are a little easier to manage.
Below is an example script breaking it down:
-- Create 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 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 View on top of the Test Table AFTER the role was
-- defined/created to show this applies to any new views made under this schema
CREATE VIEW viewSchema.testView
AS
SELECT ID
, VALUE
FROM dbo.Test
GO
-- Create a new Test User
CREATE USER testuser WITHOUT LOGIN
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 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
Your output should look as follows, confirming you are unable to select from the base table while having no issues selecting from the view that references it:
(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)
The beauty here is any user that is part of the db_viewreader role will have SELECT privileges to any new view you place into the viewSchema schema automagically which is what I assume you're looking for.
