During a session at the recent GPUG Summit, an attendee asked how to find out which users had access to which database objects. (This was in the context of a conversation about setting up SSRS and using the various database roles that GP provides for reporting.)
I responded that you can find this out using the system views which are part of SQL Server. To demonstrate the point, I’ve created two queries below. You can combine them, but for illustrative purposes I’ve divided the process into two steps.
Step 1: Find out which roles have access to which objects
The purpose of the first query is to find all objects that the given role has access to. In this example, I’m selecting by role using the “rpt_accounting manager” role. You might also select by permission type (e.g. UPDATE, INSERT OR DELETE permissions)—and thereby tell auditors that only one role has rights to perform those actions.
Here’s a sample of the output:
Step 2: Show all users assigned to a role
The second query takes a role and finds all the users assigned to it. Please note: Theoretically, roles can be assigned to other roles and those roles assigned to yet other roles. So, this one-level query won’t work in all circumstances. But for most folks, this will work just fine.
The Microsoft website has a lot of documentation on system views. Search for any of the views used in my queries above with T-SQL, and you’ll find what you need. (Note: For a “sys.” search, Google “sys objects,” otherwise you end up on the wrong page).
If you’d like to play around with this sample data and queries, you can download our speadsheet.
Sign up for our newsletter by entering your email in the box on the top right of the page.