locked
VIEW DEFINITION database permission RRS feed

  • Question

  • Am I looking at this wrong or is this how it is supposed to be?

    Why is it that if I do

    GRANT VIEW DEFINITION TO some_db_user

    the properties window in Mgmt Studio for some_db_user doesn't show this in Securables? Don't you think this is confusing? If someone is not familiar with system views and doesn't know to a query like

    select p.name, perm.*
    from sys.database_permissions perm
    join sys.database_principals p
    on p.principal_id = perm.grantee_principal_id
    where
    perm.permission_name = 'VIEW DEFINITION'

     

    then you would never know that some_be_user had this permission.

    Tuesday, November 2, 2010 3:05 PM

Answers

  • I believe that is right.  fn_my_permissions() is a built-in function on SQL Server.   It reports on the accumulated rights that you have, you need to control the detail,  but it is for one person at a time.  (Thus the name 'my'.)

    Of course, you are right.  If you understand the hierarchy, you can roll you own query with the views provided and get everything that you need.

    All the best,
    RLF

    • Marked as answer by DoolinDalton Thursday, November 4, 2010 5:27 PM
    Wednesday, November 3, 2010 9:02 PM

All replies

  • I don't know if it's "supposed" to be this way, but I know that actually seeing anyone's full effective permission list has always been problematic.

    And in this case, what is the specific "securable" that you have granted access to?


    HTH, Kalen Delaney www.SQLServerInternals.com
    Tuesday, November 2, 2010 3:22 PM
  • I don't know if it's "supposed" to be this way, but I know that actually seeing anyone's full effective permission list has always been problematic.

    And in this case, what is the specific "securable" that you have granted access to?


    HTH, Kalen Delaney www.SQLServerInternals.com

     

    It wasn't a particular securable. I granted view definition on at a DB level to a certain db role. When I did that, I don't see it come thru visually in that db role's property window.

    On the other hand, it seems that if I individually select all db objects in the securables, then I get to check "view definition' permission. But if I just wanted to do it in one sweep for ALL db objects (without selecting all types) then I can only do it with the grant command (which has the down side of not showing thru the properties window).

    I could be wrong, but that is my observation so far.

     

    EDIT: I'm on SQL Server 2005. I keep forgetting to mention this.

    Tuesday, November 2, 2010 4:21 PM
  • Since you are granting view definition to all objects in the DB, it will not show up in the securables.

    To see this, 

    1. Go to that DB Properties.

    2. Select the Permissions Tab

    3. Click on the user 

    4. Check the down pane for the permission that this user have


    Regards,

    Sandesh Segu

    http://www.SansSQL.com

    SansSQL

    ↑ Grab this Headline Animator

    Wednesday, November 3, 2010 3:27 PM
  • Hmmm, another place where you have database permission information. So then, would anyone experienced in SQL Server permissions agree that the best place (and the one place) to see all permissions is thru the system view sys.database_permissions?
    Wednesday, November 3, 2010 4:16 PM
  • The sys.database_permissions is a fine place to see the permissions granted.  However, this is only directly granted rights.  As Kalen mentioned earlier, seeing all rights available is very difficult do to the inheriting of rights.  See:

    http://msdn.microsoft.com/en-us/library/ms191465.aspx

    As you can see, there are many ways that you can gain rights, that are not directly visible in sys.database_permissions or sys.server_permissions.  One simple example:

    1. Login DOMAIN\JOE is a member of role SALES
    2. SALES has been GRANTed EXECUTE on domain dbo.

    Querying sys.database_permissions will not show that DOMAIN\JOE has execute permissions on the store procedure dbo.UpdateProductPrince.  But he does, since he inherited it through the role membership.

    There is also a function fn_my_permissions() that will pursue the chain of rights to give you a more detailed view.

    Perhaps fn_my_permissions() is what you are looking for.

    RLF

    Wednesday, November 3, 2010 7:37 PM
  • The sys.database_permissions is a fine place to see the permissions granted.  However, this is only directly granted rights.  As Kalen mentioned earlier, seeing all rights available is very difficult do to the inheriting of rights.  See:

    http://msdn.microsoft.com/en-us/library/ms191465.aspx

    As you can see, there are many ways that you can gain rights, that are not directly visible in sys.database_permissions or sys.server_permissions.  One simple example:

    1. Login DOMAIN\JOE is a member of role SALES
    2. SALES has been GRANTed EXECUTE on domain dbo.

    Querying sys.database_permissions will not show that DOMAIN\JOE has execute permissions on the store procedure dbo.UpdateProductPrince.  But he does, since he inherited it through the role membership.

    There is also a function fn_my_permissions() that will pursue the chain of rights to give you a more detailed view.

    Perhaps fn_my_permissions() is what you are looking for.

    RLF


    Russell, Yes I did consider that kind of a scenario, but the information on this recursive type of permissions can be retrieved from sys.database_role_members. It may be complicated to retrieve and organize, (perhaps requiring CTEs) but the information is all there. And maybe that's what you mean when you refer to the custom function.
    Wednesday, November 3, 2010 8:02 PM
  • I believe that is right.  fn_my_permissions() is a built-in function on SQL Server.   It reports on the accumulated rights that you have, you need to control the detail,  but it is for one person at a time.  (Thus the name 'my'.)

    Of course, you are right.  If you understand the hierarchy, you can roll you own query with the views provided and get everything that you need.

    All the best,
    RLF

    • Marked as answer by DoolinDalton Thursday, November 4, 2010 5:27 PM
    Wednesday, November 3, 2010 9:02 PM
  • Thank you. You did answer my opriginal question, but did you say fn_my_permission() is a built in function? That must be a 2008 thing because I don't see it in my 2005.
    Thursday, November 4, 2010 5:27 PM
  • No, it is a 2005 function.  I don't know why you cannot see it, but here is a link to the 2005 Books Online entry.

    http://msdn.microsoft.com/en-us/library/ms176097(SQL.90).aspx

    RLF

    Thursday, November 4, 2010 6:17 PM