sqlsql-server-2012

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


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'

Solution

  • 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