trace which user dropped some tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

trace which user dropped some tables

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2014-08-14 : 17:29:09

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

157 Posts

Posted - 2014-08-15 : 22:42:59

good night, is it possible to find out by auditing tables or store procedures system catalog ?

i appreciate your help in advanced

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-08-16 : 03:00:28

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

alejo46
Posting Yak Master

157 Posts

Posted - 2014-08-19 : 21:07:32

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

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-20 : 08:02:04

also check the standard reports available in SSMS,there is one called schema changes report

Javeed Ahmed

alejo46
Posting Yak Master

157 Posts

Posted - 2014-08-20 : 10:38:20

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

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-20 : 10:53:10

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

alejo46
Posting Yak Master

157 Posts

Posted - 2014-08-20 : 11:57:08

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

alejo46
Posting Yak Master

157 Posts

Posted - 2014-08-20 : 17:28:04

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 ?

alejo46
Posting Yak Master

157 Posts

Posted - 2014-08-20 : 21:00:50

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

alejo46
Posting Yak Master

157 Posts

Posted - 2014-08-21 : 11:33:54

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-21 : 12:37:13

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/

alejo46
Posting Yak Master

157 Posts

Posted - 2014-08-22 : 21:23:30

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-22 : 21:32:19

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources