December 21, 2016 at 9:31 am
Symptom: We add ~1500 users via the SSRS 2016 report portal to a folder via the security tab. Viewing the security tab at a point in the future and the users are not visible in the security tab and they do not have access to reports.
Other hints:
I look in the ReportServer DB and the users are present in the dbo.Users table. However, the linkage is 'broken' between the Users, Policies and Roles because there are no records in the PolicyUserRole table for the users who "disappeared".
Users with access to other folders are not affected.
Here's a code snippet showing the relationship between Users, PolicyUserRole, Policies, Roles, and Catalog tables:
select C.UserName, D.RoleName, D.Description, E.Path, E.Name
from dbo.PolicyUserRole A
inner join dbo.Policies B on A.PolicyID = B.PolicyID
inner join dbo.Users C on A.UserID = C.UserID
inner join dbo.Roles D on A.RoleID = D.RoleID
inner join dbo.Catalog E on A.PolicyID = E.PolicyID
order by C.UserName
Server setup:
SSRS 2016
Native mode.
Using a custom security extension to support our single sign on system.
Any hints as to why this is happening would be very helpful. Perhaps someone has seen this behavior before? And if someone can point me in a safe direction on how to manage the ReportServer DB to restore the users (who are in the dbo.Users table) that will be very helpful so we don't have to add the users again via the Report Manager interface.
BTW - tried using the RS.exe utility to batch add users, but it won't connect to the SSRS instance which I suspect is becasue we're using 'custom' authentication with the custom security extension. But still trying to work through that by creating an app which uses the SSRS Web Services directly.
Thanks in advance to anyone who spends time on this.
PaulG
P Goldy
December 21, 2016 at 3:26 pm
pgoldy (12/21/2016)
Symptom: We add ~1500 users via the SSRS 2016 report portal to a folder via the security tab. Viewing the security tab at a point in the future and the users are not visible in the security tab and they do not have access to reports.Other hints:
I look in the ReportServer DB and the users are present in the dbo.Users table. However, the linkage is 'broken' between the Users, Policies and Roles because there are no records in the PolicyUserRole table for the users who "disappeared".
Users with access to other folders are not affected.
Here's a code snippet showing the relationship between Users, PolicyUserRole, Policies, Roles, and Catalog tables:
select C.UserName, D.RoleName, D.Description, E.Path, E.Name
from dbo.PolicyUserRole A
inner join dbo.Policies B on A.PolicyID = B.PolicyID
inner join dbo.Users C on A.UserID = C.UserID
inner join dbo.Roles D on A.RoleID = D.RoleID
inner join dbo.Catalog E on A.PolicyID = E.PolicyID
order by C.UserName
Server setup:
SSRS 2016
Native mode.
Using a custom security extension to support our single sign on system.
Any hints as to why this is happening would be very helpful. Perhaps someone has seen this behavior before? And if someone can point me in a safe direction on how to manage the ReportServer DB to restore the users (who are in the dbo.Users table) that will be very helpful so we don't have to add the users again via the Report Manager interface.
BTW - tried using the RS.exe utility to batch add users, but it won't connect to the SSRS instance which I suspect is becasue we're using 'custom' authentication with the custom security extension. But still trying to work through that by creating an app which uses the SSRS Web Services directly.
Thanks in advance to anyone who spends time on this.
PaulG
One possibility might be to restore the ReportServerDB to a test instance, from a backup taken while the users were functional, and compare the tables, and see if you can update the original tables with values derived from the restored tables. That might avoid the grief associated with restoring the database over the original. Not sure if you'll be able to do that or not. In any case, have you searched the error logs for anything? You might also wonder if maybe somebody had an UPDATE or DELETE coded and affected more records than they planned...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 21, 2016 at 4:39 pm
Hi Steve. Thanks for the suggestions. I like the table comparison idea between the DBs. We might give that a shot. Checked the log files and nothing stood out, but will keep looking at that.
PaulG
P Goldy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply