|
how to find which user dropped a table in a database.
(comments are locked)
|
|
In SSMS you can try right-clicking on the dB and navigating through Reports -> Standard Reports -> Schema Changes History.
Note that this is based on the default trace so your mileage may vary. +1 for a good option and with pictures. We ought to use them more.
Oct 13 '10 at 10:18 AM
Fatherjack ♦♦
Pictures? You can put pictures in this thing? Who knew! ;-)
Oct 13 '10 at 12:35 PM
ThomasRushton ♦
@Fatherjack, definitely. Pictures are good. @Blackhawk, I know you said it in your answer, but it's worth really highlighting that this depends on the default trace...
Oct 13 '10 at 12:57 PM
TimothyAWiseman
thank you very much.....
Oct 14 '10 at 12:57 AM
Murali
There is an impact, because actions are being "watched" and audited in the trace file. The impact is minimal though and the default trace will be running unless you explicitly turned it off. You can safely leave this turned on, unless you are experiencing problems (bottlenecks on the drive where the trace is located)
Oct 14 '10 at 01:31 AM
WilliamD
(comments are locked)
|
|
If none of the other answers help you then stand up in your office and shout "Who deleted table -inserttablenamehere- ?" and then look for the most embarrassed person avoiding your attention ... Seriously though, unless you have something that the other answers have suggested in place before the drop took place then you wont find it. The best you can do is see who has permission to drop tables from the security settings on the server/database and then send a polite email to all of those people about how the table is missing and that if anyone needs to drop an object can they make sure noone else needs it before they run the script. +1 for the office intimidation technique. It works better if you have the reputation (think ScaryDBA!).
Oct 13 '10 at 01:52 AM
WilliamD
Threatening to break an arm usually works. No +1 though 'cause I think @WilliamD nailed it.
Oct 13 '10 at 04:39 AM
Grant Fritchey ♦♦
(comments are locked)
|
|
Ading to Hakan's answer, you can use the default trace to follow DDL changes. Take a look at this article from SSC +1 for that link
Oct 13 '10 at 12:02 AM
Cyborg
With 2005 & 2008, the default trace is there. Depending on how long after the fact and how much of the activity monitored by the default trace has occurred, you should absolutely be able to get that information from the default trace. Although, I like @Fatherjack's idea too.
Oct 13 '10 at 04:37 AM
Grant Fritchey ♦♦
(comments are locked)
|
|
Its impossible to get these details unless you have a DDL audit trigger or you have enabled Auditing or Default Trace enabled. how to enable Auditing in sqlserver2008
Oct 12 '10 at 11:09 PM
Murali
Check out this technical article from microsoft http://msdn.microsoft.com/en-us/library/dd392015(SQL.100).aspx
Oct 12 '10 at 11:16 PM
Cyborg
(comments are locked)
|
|
For the default trace you can get object-Creation, object-Deletion and object-Alteration events, and from all these, you can get the ApplicationName, ClientProcessID, HostName, LoginName, LoginSid, NTDomainName, and NTUserName. In other words, they can run, but they can't hide. I use fn_trace_gettable to query the default trace to see who is fiddling with the database. There are plenty of examples of its use around.
(comments are locked)
|
1 2 next page »
