how to find which user has dropped the table in sql server 2008 r2
-
Hi Friends,
Is there any way to find which user has dropped the table. My database recovery model is simple. There is no audit enabled. Is there any possibilities to find which user has dropped the table by sql query.
Thanks in advance.
-
Could check the default trace, but thats it, if its not in the trace your out of luck.
-
Is there any other dynamic view where I can get which user has dropped the table.
-
Nope the information is not logged anywhere, so you would have to do custom auditing.
-
Like Anthony mentioned, the default trace has the information you are looking for, if not to much time has passed.
SSMS has a handy report which queries the trace for you:
Additionally, i think it's a good practice to create a view for every trace you create, so that you can more easily access it via TSQL or an applicaiton if necessary:
USE master;
declare @sql varchar(1000)
declare @path nvarchar(256)
SELECT @path = path FROM sys.traces WHERE is_default = 1
IF EXISTS(select * from master.dbo.sysobjects where xtype='V' and name='VW_DefaultTrace')
BEGIN
SET @sql = 'ALTER VIEW VW_DefaultTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path +''', default)'
exec(@sql)
END
ELSE
BEGIN
SET @sql = 'CREATE VIEW VW_DefaultTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + ''', default)'
exec(@sql)
END
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply