How to Grant User to access all the views in SQL Server but not table and stored procedures?

Question

I am trying to create a new temp user who will have only access to all views to read only.

User should not have to read access to table and stored procedures.

I tried below query but it is giving access to table as well.

EXEC sp_addrolemember db_datareader, 'user1'

GRANT VIEW DATABASE STATE TO  'user1'
GRANT VIEW DEFINITION TO 'user1'

Answer 1

The primary way to do this is to grant permissions to only the view objects. You can loop through all of the views in the db with the following:

DECLARE @sql VARCHAR(MAX)

SELECT GrantStatement = 'GRANT SELECT ON ' + TABLE_NAME + ' TO user1'
INTO #grants
FROM
INFORMATION_SCHEMA.Views

WHILE EXISTS (SELECT 1 FROM #grants)
BEGIN
    SELECT TOP 1 @sql = GrantStatement
    FROM #grants

    EXEC (@sql)

    DELETE #grants
    WHERE GrantStatement = @sql
END

A second technique would be to create a separate schema used only for views, then grant permission to the entire schema. That could be done like this:

GRANT SELECT ON SCHEMA::ViewSchema TO user1