Post reply

Database Access Rights

  • February 15, 2019 at 8:03 am

    #370711

    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

  • February 15, 2019 at 12:49 pm

    #2022186

    KGJ-Dev - Friday, February 15, 2019 8:03 AM

    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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply