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:

SELECT
        
Catalog.Name ReportName
        
,Users.UserName
        
,Roles.RoleName
  
FROM [dbo].[Catalog]
        
JOIN
        
dbo.PolicyUserRole
        
ON   [Catalog].PolicyID = PolicyUserRole.PolicyID
        
JOIN
        
dbo.Users
        
ON   PolicyUserRole.UserID = Users.UserID
        
JOIN
        
dbo.Roles
        
ON   PolicyUserRole.RoleID = Roles.RoleID



Can anyone see any problems with that?

6 comments:

Paul said...

Scott,

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...

Scott,
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!