Over the years, I’ve been asked to review what SSRS reports are being sent to whom on a regular basis, especially as individuals move between roles or leave the business. this is always a cumbersome task to do manually as in any organisation our report count consistently rises as more and more business users generate requests that have valid business reasons. As such I have strung together the following T-SQL code. In it’s current state it lists all reports that are emailed and the email address meets a certain criteria. I hope you can use this and manipulate it to meet your requirements.
I ought point out at this point, I have taken inspiration for this from other sites – it is not all my own work! It is modified by me and posted here as a useful resource!
FROM Catalog as c
INNER JOIN Subscriptions as s ON c.ItemID = s.Report_OID
INNER JOIN DataSource as d ON c.ItemID = d.ItemID
LEFT JOIN Users as u ON u.UserID = c.CreatedByID
LEFT JOIN ReportSchedule as rs ON c.ItemID = rs.ReportID
LEFT JOIN Schedule sch ON rs.ScheduleID = sch.ScheduleID
WHERE (c.Type = 2)
and s.DeliveryExtension = ‘Report Server Email’
and (s.Description like ‘%turb%’)
ORDER BY c.Name