How do I grant permissions to view users and their permissions?

Kenneth Fisher, 2018-09-17

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads