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. In my query below, I first use fn_trace_getinfo to find the location of the default trace in case it has been moved to a non-standard location.  Then I pass the trace file location to fn_trace_gettable so that the trace file can be read and queried.  I join on sys.trace_events so that I only get records that correspond to the audit events that should show me if permissions have been changed. 

I sorted my results on the StartTime column but have also provided a couple of other columns that may likely be sorted on in other situations.  Just comment out the trcdata.StartTime column and uncomment the column you might want to sort with to have results displayed in a way that makes the best sense for your stuation.

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

Here are the results that were returned and quickly dropped into a spreadsheet and sent my client:

We can see that developer2 had dropped some permissions at 2:52 pm, which caused the domain account to be unable to access the data it needed.  Of course, developer2 was talked to about changing permissions on a production database and he explained that he had thought that he was working in one of the development instances when he ran the statements.  It was a simple mistake that can be easily fixed by restoring the dropped permissions and then setting some limitations on the ability of developers to change security in production.

It is really good to know that there is some trace information that can be easily accessed so make sure to review how to get to the default trace info if you ever need to.

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