How to remove a user from a report

Question

I want to manage the permission of one report in an Windows Form Application.

And found a way to search users assigned to a report.

 SELECT C.Name 
      ,U.UserName 
      ,R.RoleName 
      ,R.Description 
      ,U.AuthType 
  FROM Reportserver.dbo.Users U 
  JOIN Reportserver.dbo.PolicyUserRole PUR 
    ON U.UserID = PUR.UserID 
  JOIN Reportserver.dbo.Policies P 
    ON P.PolicyID = PUR.PolicyID 
  JOIN Reportserver.dbo.Roles R 
    ON R.RoleID = PUR.RoleID 
  JOIN Reportserver.dbo.Catalog c 
    ON C.PolicyID = P.PolicyID 
 WHERE c.Name = @ReportName
ORDER BY U.UserName 

After that, I try to remove a user

delete from Reportserver.dbo.PolicyUserRole where ID = 'XXXXX'
delete from Reportserver.dbo.Users where UserName = 'AAA\BBB'

It works okay, And I run the select SQL above, the user was gone.

But in the management website, I still found the user under the report.

How to delete the user from the report? Is there a store procedure?

Thanks.

Answer 1

In order to manage permissions in Reporting Services you need Content Manager privilages in Reporting Services and you need to manage permissions from the RS interface and not via the database back end.

---

Please click "Mark As Answer" if my post helped. Tony C.

Answer 2

The request is :

I have a report named "Report23", the customer don't want to share the report for all people. Then he give me a list of users who has the access permission. But he also want to edit the user list himself, and I cannot give him the 'content manager' permission of the Reporting Services. So I try to delete the user assignment via a sql.

How to do in this case?

Thanks.

Answer 3

Hi Tony_Kent,

I have tested on my local environment and can reproduce the issue.

Generally, we are not suggest to do any modification to the tables in the report server DB. If you delete the user information in the related tables, although you can see this user still exists in the list of the security settings in the report manager but actually the user permission have been removed already.

In your scenario, If the customer can have full permission to the exact report "Report23", but you don't want him to have extra permission to other folders of reports in the report manager, you can only configure the security setting for the Report23 to assign the "Content Manager" role for this customer to this report(Report23) like below:
 

Then, you can provide the URL of the security setting page like above (Yellow) to the customer, the customer will have access to this page and edit to assign the role for other users who need access to this report.

As you are the Administrator, when you click the "Edit Item Security", the role assignment in the parent folders will not apply to the current report. If you click the "Revert to Parent Security", you will got the same role assignment as the parent folder(Customer will not have this permission):

If you still have any problem, please feel free to ask.

Regards,
Vicky Liu

---

Vicky Liu
TechNet Community Support