SQL Server – Grant Access to All Views in Schema Without Tables

sql server

I have tables and views in a single database under a specific schema. For simplicity, here is an example:

  • [DBXYZ].[SCHEMAXYZ].[TABLEWITHRESTRICTEDDATA]
  • [DBXYZ].[SCHEMAXYZ].[SAFEVIEW1]
  • [DBXYZ].[SCHEMAXYZ].[SAFEVIEW2]

I would like to grant SELECT access to a certain set of users to the views in schema "SCHEMAXYZ" but not the tables. I prefer not to grant access to specific views because it means more administrative maintenance in the future when new views are added.

I realize that one option is simply to use a different schema name for the views (e.g. "SCHEMAXYZ_VIEWS") but that is not my preference.

Is this possible?

Best Answer

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.

Related Question