How to get user security changes with SQL default trace

08 October,2019 by Jack Vamvas

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.

 

SELECT * FROM sys.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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to get user security changes with SQL default trace


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer