Hi,
I am tying to find who have given read only access to some user in the database. when the access given. Is it possible to find it? If yes any sample query please
Were they given read access by being added to the db_datareader role? Was this recent enough to be in one of the default trace files?
You could query the trace files looking for add member to db role event:
DECLARE @TraceFilePath nvarchar(500);
SELECT @TraceFilePath =
REVERSE(SUBSTRING(REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])), 500)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT
te.[name] as EventClassName,
tr.*
FROM fn_trace_gettable(@TraceFilePath, default) tr
INNER JOIN sys.trace_events te
ON tr.eventclass = te.trace_event_id
WHERE te.[name] = 'Audit Add Member to DB Role Event'
Sue