SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 trace which user dropped some tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 08/14/2014 :  17:29:09  Show Profile  Reply with Quote
Good afternoon
I need your help pls, is there any way to know or trace which user dropped any table by means of auditing tables, or system catalog or any other method ?

I appreciate your help in advanced

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 08/15/2014 :  22:42:59  Show Profile  Reply with Quote
good night, is it possible to find out by auditing tables or store procedures system catalog ?

i appreciate your help in advanced
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 08/16/2014 :  03:00:28  Show Profile  Visit jackv's Homepage  Reply with Quote
SQL Server has a default trace , with some good information . As long as the default trace has not rolled over. Read this link on Who dropped the table in SQL Server http://www.sqlserver-dba.com/2013/05/who-dropped-the-table-in-sql-server.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 08/19/2014 :  21:07:32  Show Profile  Reply with Quote
Good night, thanks you very much for your support,iVe got a question, i run the script and change database name 'Trafico1' db but dindt return any row, and Im completely sure in ths last 3 months many user tables have been dropped
As u mention: If a user has deleted a database object , such as a table – as long as the trace log is still available – it is easy to write a query and return the relevant information, Is the trace log unavailable ? if so how to enable it ? Thanks for your help in advanced

SELECT * FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value]
FROM sys.fn_trace_getinfo(NULL) f
WHERE f.property = 2)), DEFAULT) tf
INNER JOIN sys.trace_events te
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS tc
ON te.category_id = tc.category_id
WHERE databasename = 'TRAFICO1' AND
objectname IS NOT NULL AND
te.category_id = 5 AND
te.trace_event_id = 47
AND tf.StartTime > getdate() -1
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
675 Posts

Posted - 08/20/2014 :  08:02:04  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
also check the standard reports available in SSMS,there is one called schema changes report

Javeed Ahmed
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 08/20/2014 :  10:38:20  Show Profile  Reply with Quote
thanks, but Im newbie and how do i get SSMS -> schema changes reports ?

2nd what should i do afterward ?

thanks once again for your help because it's really important for us in order to grant or deny privileges
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
675 Posts

Posted - 08/20/2014 :  10:53:10  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
right click on the database->reports->standard reports->schema changes history
you need to grant only required privileges to the users, only admin must have the rights to perform schema changes.


Javeed Ahmed
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 08/20/2014 :  11:57:08  Show Profile  Reply with Quote
ok thanks but this options allows me only grant or revoke permissions right ?

but i need to trace which user dropped user tables and the query i run above didnt return any row and didnt give to me any useful information
about date, user login and so on

Thanks for your help in advanced
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 08/20/2014 :  17:28:04  Show Profile  Reply with Quote
OK thanks, i saw on schema changes history the DDL commands for the las 2 days since august 18th, its ok but how can i list history DDL commands from may 1 2014 to now ?
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 08/20/2014 :  21:00:50  Show Profile  Reply with Quote
Is it possible to collect this since may 2014 ?, it just it collected data from the last 2 days. Thanks in advanced

Schema Changes History
[TRAFICO1]
on TRAFICO\TRAFICOSQL at 8/20/2014 5:16:30 PM
This report provides a history of all committed DDL statement executions within the Database recorded by the default trace.
Schema Change History (Since 8/18/2014 4:48:25 AM ).

DDL Operation Time Login Name User Name
DROP 8/18/2014 11:52:52 AM NH\sqlproduccion sqlproduccion

Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 08/21/2014 :  11:33:54  Show Profile  Reply with Quote
Good morning, is there any answer? i apologize for being so persistent but i need to know if i can find out how to list out which user drooped some database objects for the last two months, and thanksfully i receive 2 options a script which i run but it didnt return any row and for SSMS but it just list out the report for the last 2 days
Thanks for your help in advanced
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 08/21/2014 :  12:37:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
Check how far back the default trace goes. If it doesn't go back to the timeframe you are wanting, then you are out of luck unless you had some other process auditing.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

alejo46
Posting Yak Master

Colombia
142 Posts

Posted - 08/22/2014 :  21:23:30  Show Profile  Reply with Quote
Good night, Thanks tkizer, could u please tell me exactly the right steps to find out how far back the default trace goes ? because i have no idea
Thanks for your help in advanced
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 08/22/2014 :  21:32:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
Look for the log*.trc files in the Log directory. On my system, this is the path: S:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log.

This might help: http://www.jasonstrate.com/2013/01/determining-default-trace-location/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000