locked
how to give access to only views to a user. RRS feed

  • Question

  • Hi !

      1)      i want to give a user to select views which are made on tables.

    that user should not have any other permission. he should only be able to select views and get data from underlying table

    1) other user should be able to manuplulate (all permissions) only on  views which are made on tables.

    thanking you

    yours sincerely


    • Edited by rajemessage Saturday, October 6, 2012 11:00 AM
    Saturday, October 6, 2012 10:57 AM

Answers

  • There is no way to grant permissions on views and nothing but views as a class of objects in one swoop.

    You can always grant permissions view per view:

    GRANT SELECT ON view1 TO thisuser
    GRANT SELECT ON view2 TO thisuser
    GRANT SELECT ON view3 TO thisuser

    And when a new view is added you need to grant permission on that view.

    There is however a solution: put all views in a schema separate from the tables. Then you can grant the user permission on that schema:

    GRANT SELECT ON SCHEMA::viewschema TO thisuser

    And to simplify for this user, you can make viewschema the default schema for this user:

    ALTER USER thisuser WITH DEFAULT_SCHEMA = viewschema


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, October 6, 2012 12:23 PM

All replies

  • There is no way to grant permissions on views and nothing but views as a class of objects in one swoop.

    You can always grant permissions view per view:

    GRANT SELECT ON view1 TO thisuser
    GRANT SELECT ON view2 TO thisuser
    GRANT SELECT ON view3 TO thisuser

    And when a new view is added you need to grant permission on that view.

    There is however a solution: put all views in a schema separate from the tables. Then you can grant the user permission on that schema:

    GRANT SELECT ON SCHEMA::viewschema TO thisuser

    And to simplify for this user, you can make viewschema the default schema for this user:

    ALTER USER thisuser WITH DEFAULT_SCHEMA = viewschema


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, October 6, 2012 12:23 PM
  • Hi Raje,

    There are many ways to grant access to views to an user.

    One of them is explained below

    Using DB Roles :

    1.You can create Database Role and that role can have Permission to Select on all views.

    Use <DB NAME>
    Go
    EXEC sp_addrole 'db_viewselect'
    GO

    Select "Grant SELECT ON ["+USER_NAME(uid)+"].["+name+"] to db_viewselect" from sysobjects where xtype='V'
    go

    --Execute the results you get for the above statement which will grant select on all the views to DB role db_viewselect.

    2.Now then add the user(s) as member of that role.

    EXEC sp_addrolemember 'db_viewselect', 'User1'
    Go


    Direct Permission to User

    You can use this statement as well

    Select "Grant SELECT ON ["+user_id(uid)+"].["+name+"] to User1" from sysobjects where xtype='V'

    • Proposed as answer by Deepak DBA Tuesday, January 15, 2013 1:46 AM
    Friday, October 12, 2012 2:30 AM
  • Deepak,

    I tried the steps you outlined above under Using DB Roles: but could not use the Select "Grant SELECT ON" command because an AD username was not accepted by the command.  It threw an "Incorrect syntax error" when I executed the following:

              Select "Grant SELECT ON ["+USER_NAME(hmnc\jsmith)+"].["+name+"] to db_viewselect" fromsysobjectswherextype='V'

    go

    Is there a different command I should use for an AD user?

    Wednesday, November 28, 2018 7:32 PM