May 13, 2014 at 11:26 pm
Dear,
There are three users in our database. They can connect db using SSMS. Sometimes users modify data from SSMS via "Edit Mode". Then it becomes tough for me to identify who have done the modifications. Thats why I created server side trace. From the trace file I can't find any information that what data is changed from SSMS via "Edit Mode".
Please help me to know the solution.
Thanks
Akbar
May 14, 2014 at 12:53 am
The trace will show the modifications, but it's high overhead. Maybe a trigger?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2014 at 1:15 am
It also depends on what kind of information are you capturing in your trace file? What are the counters have you opted in your trace? You can also use filter for the specific users.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 14, 2014 at 2:13 am
I capture the following event and filter in the trace file.
EXEC sp_trace_setevent @TraceID, 12,1, @on --SQL-BatchCompleted,TextData
EXEC sp_trace_setevent @TraceID, 12,6, @on --SQL-BatchCompleted,NTUserName
EXEC sp_trace_setevent @TraceID, 12,8, @on --SQL-BatchCompleted,HostName
EXEC sp_trace_setevent @TraceID, 12,10, @on --SQL-BatchCompleted,ApplicationName
EXEC sp_trace_setevent @TraceID, 12,11, @on --SQL-BatchCompleted,LoginName
EXEC sp_trace_setevent @TraceID, 12,12, @on --SQL-BatchCompleted,SPID
EXEC sp_trace_setevent @TraceID, 12,14, @on --SQL-BatchCompleted,StartTime
EXEC sp_trace_setevent @TraceID, 12,15, @on --SQL-BatchCompleted,EndTime
EXEC sp_trace_setevent @TraceID, 12,35, @on --SQL-BatchCompleted,DatabaseName
EXEC sp_trace_setevent @TraceID, 13,1, @on --SQL-BatchString,TextData
EXEC sp_trace_setevent @TraceID, 13,6, @on --SQL-BatchString,NTUserName
EXEC sp_trace_setevent @TraceID, 13,8, @on --SQL-BatchString,HostName
EXEC sp_trace_setevent @TraceID, 13,10, @on --SQL-BatchString,ApplicationName
EXEC sp_trace_setevent @TraceID, 13,11, @on --SQL-BatchString,LoginName
EXEC sp_trace_setevent @TraceID, 13,12, @on --SQL-BatchString,SPID
EXEC sp_trace_setevent @TraceID, 13,14, @on --SQL-BatchString,StartTime
EXEC sp_trace_setevent @TraceID, 13,15, @on --SQL-BatchString,EndTime
EXEC sp_trace_setevent @TraceID, 13,35, @on --SQL-BatchString,DatabaseName
EXEC sp_trace_setfilter @TraceID, 35, 0, 0, N'dbname'
May 14, 2014 at 2:18 am
GilaMonster (5/14/2014)
The trace will show the modifications, but it's high overhead. Maybe a trigger?
Yes, I found information that someone has opened Edit window but did not find what has changed. Can I write DML trigger on Database or Table?
May 14, 2014 at 3:22 am
shohelr2003 (5/14/2014)
GilaMonster (5/14/2014)
The trace will show the modifications, but it's high overhead. Maybe a trigger?Yes, I found information that someone has opened Edit window but did not find what has changed.
Because of the filter on database name most likely. Take that off and look at the events.
DML triggers go on tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2014 at 5:48 am
@gilamonster, Thank you for your valuable time and suggestion.
June 10, 2014 at 2:15 am
hi,
I once had similar issue and i used a combination of database level triggers and Server level triggers.
Foe Database level it was for checking who and when data was deleted on a table, and for Server level it was for
who or when a table structure was modified.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply