Find Permission Changes In The Default Trace

By Lori Brown | Helpful Scripts

Aug 07

— By Lori Brown  @SQLSupahStah

I recently had someone contact me who needed to figure out when a user’s database permissions were changed. Apparently, a domain account that had been working for months doing things in a specific database suddenly could not do its normal work. On checking, we found that all of the user’s permissions had been removed from the database. The permissions were reset but management wanted to know when and who changed things. All the people with access to assign permissions were asked about it and everyone swore that no changes had been made. Uh huh…

Fortunately the default trace in SQL does contain auditing events for when objects and logins are changed.

Blog_20150807_1

A quick query of the default trace files using fn_trace_getinfo and fn_trace_gettable will give you what you need.

DECLARE @tracefile VARCHAR(500)

— Get path of default trace file

SELECT @tracefile = CAST(value AS VARCHAR(500))

FROM ::fn_trace_getinfo(DEFAULT)

WHERE traceid = 1

AND property = 2

— Get security changes from the default trace

SELECT *

FROM ::fn_trace_gettable(@tracefile, DEFAULT) trcdata  — DEFAULT means all trace files will be read

INNER JOIN sys.trace_events evt

ON trcdata.EventClass = evt.trace_event_id

WHERE trcdata.EventClass IN (102, 103, 104, 105, 106, 108, 109, 110, 111)

ORDER BY trcdata.StartTime

–trcdata.DatabaseID

–trcdata.TargetLoginName

More info on fn_trace_getinfo: https://msdn.microsoft.com/en-us/library/ms173875.aspx

More info on fn_trace_gettable: https://msdn.microsoft.com/en-us/library/ms188425.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>