Identifying the user who droped or deleted a table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Identifying the user who droped or deleted a table


Hi Everyone, How can I find out which user has used ddl statement or which user has droped a table.Is this information available in sql event logs if not how can I make this information available in the sql event logs. what is the best way to obtain above information with less overhead on sqlserver.
Thanks,
Gautham.R
If you are using the SQL server 2005 you can make use of DDL triggers…
If it is not enabled then you enable it for future auditing… At this time there is no way other than sql server trace. If you are using sql 2005, by default sql server runs default trace and it keeps only 5 files with 20 MB each…check those trace file they are not over written… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Just curious How to access the default trace files? Thanks
Satya
Mohammed, where are those 5 default trace files located? /log folder? what is the default trace file extension?
I checked my server, they don’t exist ——————
Bug explorer/finder/seeker/locator
——————
Default trace file location will be log folder where your sql error log and sql agent logs will be…
It is a trace file so the extension is .trc MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Is trigger the only way to know this information. is there anyway to use profiler something else you people can think of. But how can i get the dropped table information using trigger is it possible. Thanks,
Gautham.R
Three things can provide you what has happend… 1. DDL triggers which are new in sql 2005 must have configured…if your server is not configured for ddl triggers then you don’t left with one more option…
2. Second option sql trace/profiler, it has to be running to capture… if you were not running any trace…then check default trace if it is not over written because sql keeps only five files each 20 MB size…
3. Third option will be… if you have transaction log backups you can use third party tools to read the log backup and see the problematic transaction and who did it….
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Probably a good situation to establish a tightened security policy. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
When you want to audit such collections there is no way you want to make it less stressful on the server, as it will have impact if the resources are already crunched to the limit. The best way is to collect the stats (required) on a periodic basis such as morning, afternoon and evening or even during busy times or less traffic times to get a benchmark of trace. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Hi Everyone,
In case of multiple instance how can I identify the trn file or
log file of that particular instance ( I am having sql 2000 multiple instances
as well as sql 2005 multiple instance). Can we make profiler to run according to a scheduled automatically and only for a delete statements something like that. Thanks,
Gautham.R
quote:Originally posted by r_gautham2001 Hi Everyone,
In case of multiple instance how can I identify the trn file or
log file of that particular instance ( I am having sql 2000 multiple instances
as well as sql 2005 multiple instance). Can we make profiler to run according to a scheduled automatically and only for a delete statements something like that. Thanks,
Gautham.R
Trn file means transaction log backup file? if yes then you use restore headeronly command to check the source(instance) of the file belogns to… Yes, you can filter the trace…. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
By default you have different directories for each SQL instance and if you are performing backups to the database on that instance then you would have named them appropriately. You can take help of Profiler but running them on all the instances is troublesome if your server is already looking for resources, either you have to depend upon the third party tools or take help (intermittent) of AUDIT LOGON trigger. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>