tl;dr; VIEW DEFINITION
Every now and again you’ll have a user that needs to be able to see what permissions other users have. Not change them, just look at them. In the cases I’ve seen it’s usually a manager or something similar reviewing the database permissions. Or maybe someone doing an entitlement review (checking to make sure everyone has the permissions they need, and just the permissions they need).
And, if you’ve made it this far, you read the first line and you know that the permission required is VIEW DEFINITION. What’s interesting is that this permission is usually used to grant someone the ability to look at the code behind T-SQL code objects. SPs, Views, Functions etc. But, it turns out that principals also have the VIEW DEFINITION permission. In this case, it allows the user (the person, not the principal) to view the permissions related with the principal they are granted VIEW DEFINITION to.
In this example [Tin
] can view the instance level permissions of [Cowardly
].
This is really cool, right? Well, there are some limitations.
- Because principals are not contained by schemas or anything else, granting permissions to groups of principals is impossible except at very specific levels.
- Individual Principal Level: VIEW DEFINITION on the principal.
- All principals in a database: VIEW DEFINITION on the database. This also grants access to view the code in the database.
- All principals on the instance: VIEW ANY DEFINITION on the instance. This also grants access to view all the code and all principals in every database.
- While you can grant VIEW DEFINITION on user defined roles, you can’t grant it on built in roles.
In the end, I find this a highly useful piece of information, if somewhat limited. I could wish for a separation between viewing code and viewing principal information and in particular, the ability to grant this permission in more granular groups rather than the all or nothing it is now.
