Friday, February 25

Report Permissions

A reader of this blog asked me this morning: "If you wanted to design a query to make a report for management that would show which AD Groups or users have access to which reports, how would you go about it."

This is what I quickly whipped up:

Catalog.Name ReportName
FROM [dbo].[Catalog]
ON   [Catalog].PolicyID = PolicyUserRole.PolicyID
ON   PolicyUserRole.UserID = Users.UserID
ON   PolicyUserRole.RoleID = Roles.RoleID

Can anyone see any problems with that?


Paul said...


Great response time.
I'll try it Tomorrow and see if it works. Thanks a million. With upwards of 300 reports on the development blocks this will be a lifesaver.

Scott Herbert said...

Please let me know how you go!

Paul said...

Worked like a charm, it actually pulled all objects in, data sources, folders, and reports. Added a Grouping to the Stored Procedure for ease at report time. I try to use AD groups when assigning rights to reports rather than Individual users, but AD groups are stored on the server as well, so with a join to that, I can show the users of the AD groups. It actually showed reports that I have deleted which I thought was a little odd. This will certainly come in useful for others who stumble across this blog

Scott Herbert said...

Awesome, glad I could help.

Mike said...

Great stuff Mr. Ninja. This is close to what I thought I needed, but the AD groups still hinder me.

I am trying to create a RS report that would allow a user to search for any report that uses a particular field they search for. I have a report that searches the XML in Catalog for the fieldname and it works great. However, I only want it to show results for reports the user actually has access to. How would I determine this from within an RS report?

Scott Herbert said...

Hi Mike,

I think you'd need some sort of provider or service (which allows you to query the AD with a user's name) outside the boundaries of what can be done in plain SQL and rdl to achieve this. Interesting question; I'll think more on it!