January 14, 2010 at 1:17 pm
Recently I was analyzing a report/query which returns a list of users by role for a particular folder on the Report Manager. My issue is that my query returned two users that should not have access. I then navigated to the particular folder in the Report Manager to view the security. To my surprise, the users were not visible.
Assuming my query is correct, how can this be? I assume that the dbo.PolicyUserRole table ultimately controls what items for a user by role that they have access to. Based on what I visibly see in the security property in Rpt Mgr, the associated records in dbo.PolicyUserRole for my issue should not exist.
Here is a snippet of my query...
SELECT UserName, Path, RoleName
FROM dbo.Catalog
INNER JOIN dbo.PolicyUserRole ON dbo.PolicyUserRole.PolicyID = dbo.Catalog.PolicyID
INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.PolicyUserRole.UserID
INNER JOIN dbo.Roles ON dbo.Roles.RoleID = dbo.PolicyUserRole.RoleID
WHERE Type = 1 and Path <> ''
ORDER BY Path, UserName
Please share any thoughts, concerns, or questions that you may have.
Thank you in advance,
jb
February 11, 2010 at 1:57 pm
Any thoughts on this? We are still struggling for a solution.
Thanks,
jb
February 12, 2010 at 7:46 am
I have a script that I did that is similar to yours I have an additional join in it.
try this one:
SELECT UserName, Path, RoleName
FROM dbo.Catalog
INNER JOIN dbo.Policies on dbo.Policies.PolicyID = dbo.Catalog.PolicyID
INNER JOIN dbo.PolicyUserRole ON dbo.PolicyUserRole.PolicyID = dbo.Policies.PolicyID
INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.PolicyUserRole.UserID
INNER JOIN dbo.Roles ON dbo.Roles.RoleID = dbo.PolicyUserRole.RoleID
WHERE Type = 1 and Path <> ''
ORDER BY Path, UserName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply