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
YES, there is a way to find out Who did it.
On SQL Log file viewer, on left panel select Windows NT to expand and select Application to display.
You can see details of date, User, computer, etc:
message:
Date 8/02/2014
Log Windows NT (Application)
Source MSSQLSERVER
Category (2)
Event 2342
User ??????? THIS IS WHO "OFFENDER"
Computer THIS IS THE HOST where he did it
Message
Configuration option 'min server memory (MB)' changed from 2048 to 512. Run the RECONFIGURE statement to install.
Good luck.
Best Answer
If the correct auditing is turned on, and the security event log hasn't rolled over it'll be in there. Failing that it may not be.
You could look at NTFS file permissions and ownership, and what accounts have sysadmin rights and possibly figure it out.