July 31, 2014 at 4:20 pm
Hi friends,
user need access to check which user has executed delte/drop queries.
for this which access we need to give to that user..
need help asap..
Thanks,
SAM
Regards,
SAM
***Share your knowledge.It’s a way to achieve immortality----Dalai Lama***
August 1, 2014 at 12:55 am
Unless your tracing all your queries you wont know about deletes, for drops if your quick enough you could look inside the default trace, but that gets overwritten quite quickly so you would need a process to load in the trace files before they get overwritten.
Easiest option is to put a DDL trigger on the databases in question for the DROPs and then put DML triggers on the tables you want to know who's deleting from to farm out the username and a timestamp when a record is deleted. But this will be a going forward audit, not a back in time audit.
August 1, 2014 at 1:58 am
Create a trace and filter all keywords (insert/update/dete etc) and log to a table....
Cheers
August 1, 2014 at 2:19 am
The easiest tool for this job is an audit.
More information can be found here: http://msdn.microsoft.com/it-it/library/cc280386.aspx
-- Gianluca Sartori
September 8, 2014 at 6:46 am
You could give the user the ALTER TRACE permission and let them run the Profiler when they need it.
Just be sure to tell them how NOT to use it first though!
September 29, 2014 at 11:13 am
I picked up this technique from a Kalen Delaney article that I can't seem to find now. Might not help if it was not a recent transaction.
DECLARE @a NVARCHAR(MAX) = N''
DECLARE @b-2 VARBINARY(MAX)
SELECT @a = [Transaction ID] FROM fn_dblog(NULL, NULL) WHERE [Transaction Name] = N'DROP'
SELECT @b-2 = MAX([Transaction SID]) FROM fn_dblog(NULL, NULL) WHERE [Transaction ID] = @a
SELECT SUSER_SNAME(@b)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply