How to give access to all Views in a DB

  • Srinivas Sista

    SSC Enthusiast

    Points: 120

    Hi,

    I have scenario that i need to give access  (select permission) to all views in a DB. I have given read permission to all tables, for windows AD account by which he is able to do select on all the view. Same user I have created SQL login and given read permission on the DB however he is good with tables to select but not view to select.

    Version of 13.0

    Question: is there any script which allows us to give select permissions to all views in a DB for both Windows ID (EX:- Company Domain\user name ) and SQL login (EX:- ABC). also you can reply to sns_subash2002@yahoo.com

    As it is production hope to see a response from you all>

    Thanks,

    Srinivas

  • Mr. Brian Gale

    SSC-Insane

    Points: 22759

    Pretty sure putting the logins in the datareader role should handle this.

    alternately, you could generate the script it with something like:

    SELECT N'GRANT SELECT ON ' + name + ' TO USER' AS ToRun FROM [sys].[views]

    Replacing "user" with the user you want to grant that on.  Toss that into a cursor, then have a loop through the cursor executing the script.  Something like:

    DECLARE @user NVARCHAR(255) = N'USER';
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE [curse] CURSOR LOCAL FAST_FORWARD FOR
    SELECT
    N'GRANT SELECT ON ' + [name] + ' TO ' + @user + ';' AS [ToRun]
    FROM [sys].[views];
    OPEN [curse];
    FETCH NEXT FROM [curse]
    INTO
    @SQL;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    --EXEC [sys].[sp_executesql]
    -- @SQL;
    PRINT @SQL;
    FETCH NEXT FROM [curse]
    INTO
    @SQL;
    END;

    replace"USER" with the name of the user you want to grant it to and uncomment the EXEC command if you want it to actually run.  Otherwise it just prints the commands to run to the messages tab.

    NOTE - the above assumes all of the views are in the default schema.  If not, you'd need to look up the schema for those as well.

    This will work for all views in that database.

    • This reply was modified 5 days, 3 hours ago by  Mr. Brian Gale. Reason: alternate option
    • This reply was modified 5 days, 3 hours ago by  Mr. Brian Gale. Reason: fixing typo
    • This reply was modified 5 days, 3 hours ago by  Mr. Brian Gale. Reason: added NOTE
  • Srinivas Sista

    SSC Enthusiast

    Points: 120

    Hi,

    Thanks for the help. this was useful.

    Thanks,

    Srinivas

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply