May 8, 2017 at 2:36 am
We have had a situation where the permissions from an account disappeared. I have managed to use the default trace to see when changes to the account was made and who by, but i can only capture the actual code for adding permissions to accounts, not taking permissions away.
Is there a way to capture the code that was executed to remove permission?
from what i can see, eventclass109 is for permission changes, and it looks like EventSubClass 4 is for taking permission off, and EventSubClass 3 is for adding permission.
code used below:
DECLARE @tracefile VARCHAR(5000)
-- 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, 113)
--where name = 'Audit Add DB User Event'
ORDER BY trcdata.StartTime
May 8, 2017 at 5:57 am
If you want to monitor for specific security changes, I'd suggest using extended events and then you'll need to filter for statements that are changing the permission on the user. There's not a specific event that will capture security changes (or at least, none I'm aware of).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply