SQL Server – Getting Referenced Object from SQL Trace

sql server

I am able to query the default trace or any SQL trace for the event "Audit Add Role Event" to see if anyone has received any instance level access escalation. What I am trying to figure out is how to get which specific user got added to the role. I am using the query below… I tried using the referenced object column but found that it was null.

SELECT TOP 1    TraceFile.[value]
                FROM sys.fn_trace_getinfo(NULL) TraceFile
                WHERE TraceFile.property = 2
---------------------------------------------------------
SELECT  TraceEvent.name AS [EventName],
        Trace.DatabaseName,
        Trace.DatabaseID,
        Trace.NTDomainName,
        Trace.ApplicationName,
        Trace.LoginName,
        Trace.SPID,
        Trace.Duration,
        Trace.StartTime,
        Trace.EndTime
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), (SELECT TOP 1    TraceFile.[value]
            FROM sys.fn_trace_getinfo(NULL) TraceFile
            WHERE TraceFile.property = 2
                                             )), 4) Trace
    JOIN sys.trace_events TraceEvent ON Trace.EventClass = TraceEvent.trace_event_id
WHERE   TraceEvent.name LIKE 'Audit Add Role Event'
ORDER BY Trace.StartTime;

Best Answer

Audit Add Role Event is actually auditing the addition of a new role (e.g. CREATE ROLE). To audit adding a new member to an existing role, you want either 108 - Audit Add Login to Server Role Event or 110 - Audit Add Member to DB Role Event, depending on which type of role you actually mean.

Here's an example of each:

  • Database user added to database role:

    DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT RoleName, TargetUserName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 110
-- AND DatabaseName = N'YourDatabase'
ORDER BY StartTime DESC;

  • Login added to server role:

    DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT RoleName, TargetLoginName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 108
ORDER BY StartTime DESC;

You can also add other columns from the trace to see what application, host, and login actually performed the action. Combining the two queries above, and adding auditing columns:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  RoleName, TargetUserName, TargetLoginName, DatabaseName, 
  ApplicationName, HostName, LoginName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (108, 110)
AND COALESCE(DatabaseName, N'?') = CASE EventClass 
--   WHEN 110 THEN N'YourDatabase' ELSE N'?' END
ORDER BY StartTime DESC;

Now when I do something like this on my system:

USE master;
GO
CREATE LOGIN floob WITH PASSWORD = 'x', CHECK_POLICY = OFF
ALTER SERVER ROLE [dbcreator] ADD MEMBER floob;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER blat WITHOUT LOGIN;
ALTER ROLE db_datareader ADD MEMBER blat;

I can then run the above query, and I get the following results (leaving audit columns out of the output for brevity):

RoleName       TargetUserName  TargetLoginName  DatabaseName  ...audit columns...
-------------  --------------  ---------------  ------------
db_datareader  blat            NULL             splunge      
dbcreator      NULL            floob            master

Clean up:

DROP USER blat;
GO 
USE master;
GO
DROP DATABASE splunge;
DROP LOGIN floob;

Note that this will only tell you about events that haven't been aged out of the default trace, so if you're trying to find out when someone was added to a role last year, it's unlikely you're going to find it.

Also, as an aside, it's much more efficient to hard-code the event IDs in the query than to go look up the classes every time. You can see how to get the list of available events in any trace here (as well as examples of other information you can get from the default trace).

And finally, your TOP 1 without ORDER BY assumes that the default trace will always come out first. This is not necessarily always going to be true, even if it is what you observe most of the time (see #3).

Related Question