0

What permission can grant the right to view all the permissions on a given database?

In my current context, the support team of a piece of software need to be able to investigate the cause of permission problems, without being allowed to modify the permission themselves. I want them to be able to view the permissions of every login/user/roles of a database. If it's impossible on a database level, server level would work.

3

According to the documentation for documentation for the sys.database_permissions DMV:

Any user can see their own permissions. To see permissions for other users, requires VIEW DEFINITION, ALTER ANY USER, or any permission on a user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role (such as public).

And, according to the documentation for permissions on users, you can grant view definition on the user.

If you use anything remotely complex with your permissions though, I will say from experience that the ability to impersonate a user is immensely helpful in troubleshooting such things (as you can invoke things like fn_my_permissions, sys.login_token, and sys.user_token).

  • Impersonating a user is a powerful indeed, but in my context it's not possible. Being able to see the permission with the View Definition will actually be sufficient. J'll try that! Thanks :) – Tipx Mar 13 '15 at 20:36
  • Sure. Only you know what's appropriate for your environment. – Ben Thul Mar 13 '15 at 22:10
1

This would be obtained by the VIEW DEFINITION permissions.

However you probably don't want to allow them access to your database servers.
An alternative way would be to query the permissions on your database, and supply them with the output.

This can be done using DMV's or SQL queries. There are some examples in this StackOverflow post.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.