April 17, 2009 at 3:20 am
Hi Experts,
Some one deleted entries from the table and I need to know which user has done this.
Kindly advise asap.
Thanks
CH&HU
Cheers,
- Win
"Dont Judge a Book by its Cover"
April 17, 2009 at 3:38 am
Hi,
For this table you maintain any audit trial ie maintains any audit table?
ARUN SAS
April 17, 2009 at 3:42 am
Unless you have installed some triggers, it's almost impossible te determine who did the job.
Instead of looking for the person who did this, I suggest to focus on getting the data back (restore?) and review the security settings / procedures
Wilfred
The best things in life are the simple things
April 17, 2009 at 3:43 am
CH&HU (4/17/2009)
Hi Experts,Some one deleted entries from the table and I need to know which user has done this.
Kindly advise asap.
Thanks
CH&HU
if you maintain the trace file, you can find out.
write down a trigger on the table and track the audit information.
April 17, 2009 at 3:44 am
By default you cannot track this information anywhere, after the incident occured. You can implement either triggers or run a profiler to track this information going forward.
However for the records already deleted, we do not have any such mechanism in place..
-Rajini
April 17, 2009 at 3:49 am
Dear all,
Thanks for the replies,
We donot have audit tables or triggers for the same.
Can anyone please suggest me that how can i record / track - the insert / delete / alter ??
Is there any way to trace this by writing the triggers etc ?? as am not aware of the same ....
Cheers,
- Win
"Dont Judge a Book by its Cover"
April 17, 2009 at 3:50 am
Yes the first step is to enable ( sure to create before ) trigger for any update, delete or insert record from the users!? This is the simple way to see what happened with your data in DB! Tracking for any changes in DB is not active by default!
April 17, 2009 at 5:17 am
Would the default trace have that information ?
April 17, 2009 at 5:32 am
Unlikely. The default trace has a minimum of events. I don't believe that the DML events are among them. It would be just too much activity for what's supposed to be a light-weight trace
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
April 17, 2009 at 6:45 am
homebrew01 (4/17/2009)
Would the default trace have that information ?
no, unfortunately.
the default trace tracks DDL stuff...who created /altered/deleted database objects...it doesn't have anything related to DATA changes. you need a separate trace for DML changes, like tracing on SQL:BatchCompleted, which i always include when i'm profiling a database.
i slapped together a stored proc which creates a trace for Data Manipulation, but all that trace does is track WHO ran which statement, as well as performance stuff to see what runs slow...it still wouldn't let you recover the missing data; you'd still have to get that from backups or an audit functions.
if anyone has a better suggestion for this proc, let me know.
run it in master, and it creates a view with the trace it builds.
USE master
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_AddMyTrace]
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
declare @sql varchar(1000)
declare @path nvarchar(256)
declare @traceidout int
declare @maxfilesize bigint
declare @maxRolloverFiles int
declare @on bit
set @on = 1
set @maxRolloverFiles = 2
set @maxfilesize = 50
--we want the current trace folder
--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1
SET @path = @path + N'MyDMLtrace' --system appends .trc automatically for the filename
--create the trace
exec sp_trace_create @traceidout output, @maxRolloverFiles, @path, @maxfilesize, NULL
--for the Event Every SQL statement completed, capture all 64 columns of accessible data
exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData
exec sp_trace_setevent @traceidout, 12, 2, @on --SQL:BatchCompleted,BinaryData
exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID
exec sp_trace_setevent @traceidout, 12, 4, @on --SQL:BatchCompleted,TransactionID
exec sp_trace_setevent @traceidout, 12, 5, @on --SQL:BatchCompleted,LineNumber
exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName
exec sp_trace_setevent @traceidout, 12, 7, @on --SQL:BatchCompleted,NTDomainName
exec sp_trace_setevent @traceidout, 12, 8, @on --SQL:BatchCompleted,HostName
exec sp_trace_setevent @traceidout, 12, 9, @on --SQL:BatchCompleted, ClientProcessID
exec sp_trace_setevent @traceidout, 12,10, @on --SQL:BatchCompleted,ApplicationName
exec sp_trace_setevent @traceidout, 12,11, @on --SQL:BatchCompleted,LoginName
exec sp_trace_setevent @traceidout, 12,12, @on --SQL:BatchCompleted,SPID
exec sp_trace_setevent @traceidout, 12,13, @on --SQL:BatchCompleted,Duration
exec sp_trace_setevent @traceidout, 12,14, @on --SQL:BatchCompleted,StartTime
exec sp_trace_setevent @traceidout, 12,15, @on --SQL:BatchCompleted,EndTime
exec sp_trace_setevent @traceidout, 12,16, @on --SQL:BatchCompleted,Reads
exec sp_trace_setevent @traceidout, 12,17, @on --SQL:BatchCompleted,Writes
exec sp_trace_setevent @traceidout, 12,18, @on --SQL:BatchCompleted,CPU
exec sp_trace_setevent @traceidout, 12,19, @on --SQL:BatchCompleted,Permissions
exec sp_trace_setevent @traceidout, 12,20, @on --SQL:BatchCompleted,Severity
exec sp_trace_setevent @traceidout, 12,21, @on --SQL:BatchCompleted,EventSubClass
exec sp_trace_setevent @traceidout, 12,22, @on --SQL:BatchCompleted,ObjectID
exec sp_trace_setevent @traceidout, 12,23, @on --SQL:BatchCompleted,Success
exec sp_trace_setevent @traceidout, 12,24, @on --SQL:BatchCompleted,IndexID
exec sp_trace_setevent @traceidout, 12,25, @on --SQL:BatchCompleted,IntegerData
exec sp_trace_setevent @traceidout, 12,26, @on --SQL:BatchCompleted,ServerName
exec sp_trace_setevent @traceidout, 12,27, @on --SQL:BatchCompleted,EventClass
exec sp_trace_setevent @traceidout, 12,28, @on --SQL:BatchCompleted,ObjectType
exec sp_trace_setevent @traceidout, 12,29, @on --SQL:BatchCompleted,NestLevel
exec sp_trace_setevent @traceidout, 12,30, @on --SQL:BatchCompleted,State
exec sp_trace_setevent @traceidout, 12,31, @on --SQL:BatchCompleted,Error
exec sp_trace_setevent @traceidout, 12,32, @on --SQL:BatchCompleted,Mode
exec sp_trace_setevent @traceidout, 12,33, @on --SQL:BatchCompleted,Handle
exec sp_trace_setevent @traceidout, 12,34, @on --SQL:BatchCompleted,ObjectName
exec sp_trace_setevent @traceidout, 12,35, @on --SQL:BatchCompleted,DatabaseName
exec sp_trace_setevent @traceidout, 12,36, @on --SQL:BatchCompleted,FileName
exec sp_trace_setevent @traceidout, 12,37, @on --SQL:BatchCompleted,OwnerName
exec sp_trace_setevent @traceidout, 12,38, @on --SQL:BatchCompleted,RoleName
exec sp_trace_setevent @traceidout, 12,39, @on --SQL:BatchCompleted,TargetUserName
exec sp_trace_setevent @traceidout, 12,40, @on --SQL:BatchCompleted,DBUserName
exec sp_trace_setevent @traceidout, 12,41, @on --SQL:BatchCompleted,LoginSid
exec sp_trace_setevent @traceidout, 12,42, @on --SQL:BatchCompleted,TargetLoginName
exec sp_trace_setevent @traceidout, 12,43, @on --SQL:BatchCompleted,TargetLoginSid
exec sp_trace_setevent @traceidout, 12,44, @on --SQL:BatchCompleted,ColumnPermissions
exec sp_trace_setevent @traceidout, 12,45, @on --SQL:BatchCompleted,LinkedServerName
exec sp_trace_setevent @traceidout, 12,46, @on --SQL:BatchCompleted,ProviderName
exec sp_trace_setevent @traceidout, 12,47, @on --SQL:BatchCompleted,MethodName
exec sp_trace_setevent @traceidout, 12,48, @on --SQL:BatchCompleted,RowCounts
exec sp_trace_setevent @traceidout, 12,49, @on --SQL:BatchCompleted,RequestID
exec sp_trace_setevent @traceidout, 12,50, @on --SQL:BatchCompleted,XactSequence
exec sp_trace_setevent @traceidout, 12,51, @on --SQL:BatchCompleted,EventSequence
exec sp_trace_setevent @traceidout, 12,52, @on --SQL:BatchCompleted,BigintData1
exec sp_trace_setevent @traceidout, 12,53, @on --SQL:BatchCompleted,BigintData2
exec sp_trace_setevent @traceidout, 12,54, @on --SQL:BatchCompleted,GUID
exec sp_trace_setevent @traceidout, 12,55, @on --SQL:BatchCompleted,IntegerData2
exec sp_trace_setevent @traceidout, 12,56, @on --SQL:BatchCompleted,ObjectID2
exec sp_trace_setevent @traceidout, 12,57, @on --SQL:BatchCompleted,Type
exec sp_trace_setevent @traceidout, 12,58, @on --SQL:BatchCompleted,OwnerID
exec sp_trace_setevent @traceidout, 12,59, @on --SQL:BatchCompleted,ParentName
exec sp_trace_setevent @traceidout, 12,60, @on --SQL:BatchCompleted,IsSystem
exec sp_trace_setevent @traceidout, 12,61, @on --SQL:BatchCompleted,Offset
exec sp_trace_setevent @traceidout, 12,62, @on --SQL:BatchCompleted,SourceDatabaseID
exec sp_trace_setevent @traceidout, 12,63, @on --SQL:BatchCompleted,SqlHandle
exec sp_trace_setevent @traceidout, 12,64, @on --SQL:BatchCompleted,SessionLoginName
--turn on the trace
exec sp_trace_setstatus @traceidout, 1 ---start trace
--exec sp_trace_setstatus 2, 0 ---stop trace, you must know the traceid to stop it
--exec sp_trace_setstatus 2, 2 ---close trace you must know the traceid to delete it
IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name='sp_DMLTrace')
BEGIN
SET @sql = 'ALTER VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path +'.trc'', default)'
exec(@sql)
END
ELSE
BEGIN
SET @sql = 'CREATE VIEW sp_DMLTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'
exec(@sql)
END
COMMIT TRAN
END
Lowell
April 17, 2009 at 6:56 am
There are a few log tools, such as ApexSQL's, that can read the transaction log and potentially get the data back. They won't have security information, but they'll tell you when.
April 17, 2009 at 7:03 am
Providing the log hasn't been backed up or truncated since the delete.
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
April 17, 2009 at 6:19 pm
Thanks much for your post and your procedure as well, i haven't tried today. i will try this and let you know.
Once again thanks for the replies....
Cheers,
- Win
"Dont Judge a Book by its Cover"
April 17, 2009 at 6:24 pm
Dear Lowell,
I will definitely try the procedure and will keep the improvements posted.
One more question : can't we stop a user account or login account deleting the data from a table as, if we have a user account then i need to restrict them from deleting the data from production. Either it may be by a trigger or a pop-up.
I have done the same for altering a table and dropping a table. But cannot make this for delete data.
kindly suggest me if the trigger can stop the deleting the records from the table. Can any one provide a query or trigger or anything else to stop deleting the same.
Appreciate the efforts in advance.
Thanks,
CH&HU.
Cheers,
- Win
"Dont Judge a Book by its Cover"
April 17, 2009 at 8:58 pm
CH&HU (4/17/2009)
Dear Lowell,I will definitely try the procedure and will keep the improvements posted.
One more question : can't we stop a user account or login account deleting the data from a table as, if we have a user account then i need to restrict them from deleting the data from production. Either it may be by a trigger or a pop-up.
I have done the same for altering a table and dropping a table. But cannot make this for delete data.
kindly suggest me if the trigger can stop the deleting the records from the table. Can any one provide a query or trigger or anything else to stop deleting the same.
Appreciate the efforts in advance.
Thanks,
CH&HU.
Prevent a user from deleteting from a specific table
deny delete on MyTable to MyDatabasePrincipal
Prevent a user from using a delete statement in the current database.
deny delete to MyDatabasePrincipal
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply