locked
Assigning specific user roles per view or stored proc RRS feed

  • Question

  • Hi all

    I am trying to assign roles to specific users per view or stored proc. This means they can only see they views and/or stored procs assigned to the specific scema that they belong to but we arent able to remove the "public" role that provides them with the rights to view all the views and stored procs in the Dbs.

    Does anyone have any advice???

    Thanx!

    Thursday, January 28, 2010 10:17 AM

Answers

  • Hi ,

    By default the public role has the privilege view definition granted on them , that actually gives the metadata visibility for all the users in a database . You can achieve your requirement by revoking that from your user ,

    USE

    AdventureWorks

    GO

     

    REVOKE

    VIEW Definition TO User

    --This stops the user to see other objects that is not owned by him.
    -- Revoking the same for public will in-turn revoke for all other users in your db , so make a decision on which you got to revoke view definition.


    Thanks, Leks
    Thursday, January 28, 2010 5:11 PM