I have searched the existing reports available, and I haven't been able to find what I am looking for. We want to be able to breakout all of the individual users in our nested groups. Currently, when we run the following built in default report:
Report Library > Microsoft SQL Server > Roles and Users > SQL Database Users
We are only getting groups (and some services accounts) listed, and a single account here and there. But our access is organized by group. Rarely do we give an individual access to a database. We give groups access to databases.
And we want to be able to drill all the way down to the user level. The existing report will list a group or a nested group and show that group's access, but for auditing purposes (and without have to manually use another DELL tool to list the users) we would like for the report to list the group or nested group, show their access, and then list all of the users in the group.
Is that possible?
If I understand your requirement correctly then you would like to see group memberships (direct or nested members) in the SQL Database Users report for any group listed in the report output.
The last option in the report parameters - "How would you like to handle group members" would be able to provide you memberships of the groups. You have the option to include/exclude members of domain Users group as well as if you would like to display them inline or as a subreport. Please give these options a try and let me know how that goes for you.
Thank you Aarti,
Unfortunately, that report won’t give us all of the information that we need. I may not have explained myself properly (it happens all the time!) sorry about that.
We would like a report that would allow us to simply put in the server name, and then click to run the report. The report would then output all of the roles on that SQL server, and list all of the users, and what their role is.
The existing default SQL role report works well, but it doesn’t break down all of the individual users in groups/nested groups.
I have worked with support on this issue, and they have said that nothing like that exists, and would need to be a custom report.
Hope that clears things up. Is that achievable with a custom report?
Thanks for clarifying the requirements. I'm checking few things, will let you know if it's achievable with a custom query report.
I have created a custom report for your requirement. Please follow the instructions provided with the report at the following link:
Let me know how this works for you.