30 January,2025 by Tom Collins
Question: I'm doing some sql server security privilege troubleshooting , because a customer has reported an incident - they have privileges yesterday to a certain SQL table but today those privileges no longer. I'd like to know if there is a way to identify what changes were made and by which login those changes occured. Can you supply a method ?
Answer: The SQL Server Default trace is very good for this type of reporting - although you have to capture the details fairly quickly - so the files don't roll over.
Here is a SQL query that will read the SQL Server default trace and report on certain event classes. For this query these are types of events reported
102 Audit Database Scope GDR Event
103 Audit Schema Object GDR Event
104 Audit Addlogin Event
105 Audit Login GDR Event
106 Audit Login Change Property Event
108 Audit Add Login to Server Role Event
109 Audit Add DB User Event
110 Audit Add Member to DB Role Event
111 Audit Add Role Event
The SQL query to use is :
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
here is a query to identify failed logins :
SELECT trc.* FROM fn_trace_getinfo(default) AS inf CROSS APPLY fn_trace_gettable (convert(nvarchar(255), inf.value),default ) AS trc WHERE inf.property = 2 AND inf.value IS NOT NULL AND trc.EventClass= 20 ORDER BY trc.StartTime DESC
Related links
Trace flag 610 and High performance data loading
How to use sxstrace.exe – diagnose side-by-side errors
Find who made a database name change with Object:Altered
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |