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

1

September 17, 2018 by Kenneth Fisher

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.

One thought on “How do I grant permissions to view users and their permissions?

  1. […] Kenneth Fisher shows us what we can do to grant a low-privilege user the ability to view permissions…: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,461 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: