How to get user security changes with SQL default trace

Question: There were some recent user security changes on a SQL Server login user account. Is there a way to grab this information - via the default trace? I haven't got a dedicated Extended events monitor set up - so I'm relying on the default trace . Management want to understand the details related to this security change. 

Answer: It is possible to report on security changes administered on SQL Server via the default trace. In fact - it's excellent - except if the default trace configuration is set up to rollover the files. Recently I had to use the default trace information to identify who made DDL table changes on the database.

The first step is to identify which trace events to report on . This query returns all the trace events.

It's an extensive list - but for the for the purpose of identify security on users I usually opt for the class ids between 102 and 111

102 8 Audit Database Scope GDR Event
103 8 Audit Schema Object GDR Event
104 8 Audit Addlogin Event
105 8 Audit Login GDR Event
106 8 Audit Login Change Property Event
107 8 Audit Login Change Password Event
108 8 Audit Add Login to Server Role Event
109 8 Audit Add DB User Event
110 8 Audit Add Member to DB Role Event
111 8 Audit Add Role Event

Next step is to look at creating a sql statement to generate a result set which is limited to returning events in these categories. This statement returns changes made in the previous 24 hrs. If you'd like to test , make a user permissions change , such as a security role and then execute the query 

SELECT * 
FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1

                                                              f.[value]

                                                      FROM    sys.fn_trace_getinfo(NULL) f

                                                      WHERE   f.property = 2

                                                    )), DEFAULT) tf
INNER JOIN sys.trace_events evt ON tf.EventClass = evt.trace_event_id
 WHERE tf.EventClass IN (102, 103, 104, 105, 106, 108, 109, 110, 111)
 
AND tf.StartTime > getdate() -1
 ORDER BY tf.StartTime

Read more on SQL Server default trace , audit and uses for troubleshooting

SQL Server – Audit BackupRestore Event with SQL Default trace ...

SQL Server – default trace FAQ