— 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.
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!