How list all the users/roles with granted permission(s) to a object, e.g. a view, a schema?
GetUserRolePermissions 'dbo.AView'
GetUserRolePermissions 'aSchema'
Asked
Active
Viewed 1,029 times
0
ca9163d9
- 27,283
- 64
- 210
- 413
-
possible duplicate of [SQL Server query to find all permissions/access for all users in a database](http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database) – Arindam Nayak Oct 21 '14 at 19:24
-
The link doesn't return the effective permissions. Please remove the link. – ca9163d9 Oct 21 '14 at 20:05
-
@dc7a9163d9 : I deleted my answer. Even my updated plan won't get the perspective of each role, which in some cases is all that there is. Getting effective permissions is complicated by needing to consider role/database/schema/object permissions and include the `DENY`s and maybe impersonation. It's possible but I don't have time to do it now, but will put it on my growing to-do list ;-). One thing to consider with regards to starting at the object instead of the user: different users can be in different combinations of roles, so what the roles themselves can do doesn't tell the whole story. – Solomon Rutzky Oct 22 '14 at 02:54