none
Sql Server permission Question RRS feed

  • Question

  •  

    Lets say i have a database user usrJohn.   I have a set of views i want John to see, but i don't want him to be able to see the query that builds that view, but i want that view to show up in his View list. how can I make that possible?

     

     

     

    Thursday, April 19, 2007 7:36 PM

Answers

  • For SQL 2005:

     

    GRANT usrJohn SELECT permission for the VIEW, but since he is not the VIEW owner, he will not be able to see the code. Just don't provide him with VIEW DEFINITION permissions...

    Thursday, April 19, 2007 10:01 PM
    Moderator

All replies

  • For SQL 2005:

     

    GRANT usrJohn SELECT permission for the VIEW, but since he is not the VIEW owner, he will not be able to see the code. Just don't provide him with VIEW DEFINITION permissions...

    Thursday, April 19, 2007 10:01 PM
    Moderator
  • Hmm that didn't work

     Does it matter that it is SQL 2005?

     

     

     

    Thursday, April 19, 2007 10:24 PM
  • In what Roles (custom and/or defined) does useJohn reside?

     

    The previous suggestion will work fine UNLESS you have previously give useJohn other Role memberships and/or permissions.

    Thursday, April 19, 2007 10:38 PM
    Moderator
  • Roles =public only,

    he has a schema defined to his username

    For the view in question he has all permission for the view as Deny

    except for "Control" and "Select" which he has grant

     

    Thursday, April 19, 2007 10:53 PM
  • Ok I had to deny him "Control" access..

    I went and removed that permission and it worked well..

    I appreciate your time.

    Thursday, April 19, 2007 10:55 PM
  • Control permission means that he has ALL permissions to the object. As you noted, removing CONTROL permission solves the issue.

     

    Take care...

     

    A day without learning is a day wasted...

    Thursday, April 19, 2007 11:02 PM
    Moderator
  • For anyone else reading this thread...

    You can't have the Control permission as Deny nor can you have the view definition as Deny for this to work...

     

    Wednesday, April 25, 2007 5:25 PM
  •   VIEW DEFINITION allows the principal to access the object metadata, and CONTROL permission (as Arnie and Rico already mentioned) covers all other permission on the object, and granting/denying CONTROL should be resolved at permission-check time as equivalent to having grant/deny on every permission CONTROL covers.

     

       I strongly recommend referring to BOL for understanding the permissions model (http://msdn2.microsoft.com/en-us/library/ms191291.aspx ). If you have further questions regarding permission, please let us know, we will be glad to help.

     

      An additional note: CONTROL is an extremely powerful permission. Granting CONTROL on any object is equivalent (from a security point of view, not necessarily functional-wise) to give the ownership of the object to the grantee.

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, April 25, 2007 6:34 PM
    Moderator