Forum Discussion
SSRS Sql Server 2017 expired user clean up
SSRS Sql Server 2017
We have a bunch of old AD users which expired (once employee left the org) but it still shows up for each report as created\updated by domain\nouser. They still hold all roles and ownership to the reports.
How can I do a cleanup of this expired users and update the report with genuine existing users?
I could figure out these uses exist in Users table and have a foreign key association to catalog table. I do not see any SSRS interface where I can manage these expired users.
Below Query gives me 300+ report output and these users also hold Browser, Content Manager, My Reports, Publisher and Report Builder access. We can remove it through interface but the meta data remains created by and updated by which is confusing. So we would like to remove it from Users table.
SELECT *
FROM [ReportServer].[dbo].[Catalog] as Cat left join [ReportServer].[dbo].[Users] as US
ON (cat.CreatedByID = US.UserID)
where US.UserName like '%nouser%'