Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Know user actions on database Expand / Collapse
Author
Message
Posted Tuesday, May 13, 2014 11:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
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
Post #1570636
Posted Wednesday, May 14, 2014 12:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
The trace will show the modifications, but it's high overhead. Maybe a trigger?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1570652
Posted Wednesday, May 14, 2014 1:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1570664
Posted Wednesday, May 14, 2014 2:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
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'

Post #1570676
Posted Wednesday, May 14, 2014 2:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
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?
Post #1570677
Posted Wednesday, May 14, 2014 3:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1570689
Posted Wednesday, May 14, 2014 5:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
@GilaMonster, Thank you for your valuable time and suggestion.
Post #1570774
Posted Tuesday, June 10, 2014 2:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:16 AM
Points: 180, Visits: 527
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.
Post #1579080
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse