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:
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.
Please let me know how you go!
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
Awesome, glad I could help.
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?
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!
Post a Comment