Sql-server – SQL Server Reporting Services: How to check who accessed certain report

access-controlsql serversql-server-2008-r2ssrs

I am in process of eliminating who should have access to SQL Server Reporting Services reports that I have created. Is there a way to find out who has accessed certain reports during past month?

My goal here is to remove people who hasn't accessed the report for awhile.

Best Answer

Suggest you not to remove people but disable their login or just revoke access.

Then after a full business cycle, if no one complains then remove those users.

Is there a way to find out who has accessed certain reports during past month?

Below is the query that will give the report you need. There are more report queries available at this link :

Note: Adjust the query as per your need.

 – Execution Log:



– For debugging (remove next 4 lines after pasting to dataset) –

DECLARE @DateFrom Date

DECLARE @DateTo Date

SET @DateFrom = ’2008-01-01′

SET @DateTo = ’2008-06-30′

– End debugging script –



SELECT    

      DATEPART(Hour, TimeStart) AS ReportYear

    , DATEPART(Month, TimeStart) AS ReportMonth

    , DATEPART(Day, TimeStart) AS ReportDay

    , DATEPART(Hour, TimeStart) AS ReportHour

    , Type

    , COUNT(Name) AS ExecutionCount

    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum

    , SUM(TimeProcessing) AS TimeProcessingSum

    , SUM(TimeRendering) AS TimeRenderingSum

    , SUM(ByteCount) AS ByteCountSum

    , SUM([RowCount]) AS RowCountSum

FROM

(

    SELECT TimeStart, Catalog.Type, Catalog.Name, TimeDataRetrieval,

  TimeProcessing, TimeRendering, ByteCount, [RowCount]

    FROM

    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID =

       ExecutionLog.ReportID LEFT OUTER JOIN

    Users ON Catalog.CreatedByID = Users.UserID

    WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo

) AS RE

GROUP BY

      DATEPART(Hour, TimeStart)

    , DATEPART(Month, TimeStart)

    , DATEPART(Day, TimeStart)

    , DATEPART(Hour, TimeStart)

    , Type

ORDER BY

      ReportYear

    , ReportMonth

    , ReportDay

    , ReportHour

    , Type
Related Question