Malicious attack or SQL Command?

Hi,
Yesterday here we had an incident on one of our production servers
where a few tables were explicitly dropped and recreated. I knew this
because of the object create date and the table permissions had
disappeared. This could have been a malicious attack or some process,
however my hunch is with the former.
Since the SQL Security here is going through a complete rethink (after
the horse has bolted!), my question would be other than explicitly
dropping and recreating a table is there a SQL Command/Process that
does this? Also for future reference (in case this sort of thing
happens again) I would like to setup server-side tracing, however I
have noticed that the trace doesn't pick up a users/machines IP
Address. I can see that in the SQL Error Logs use Network Address but
can a trace explicitly pick up an IP? I know SQL Server can block
certain IPs but can it log them?
Any other ideas for preventing this sort of thing would be most
welcome. Funny, you never think about security until you *REALLY* have
to. A lesson to be learned here.
Rgds,
qhMost likely this was a person with legitimate access to the SQL Server using
Enterprise Manager (AKA Enterprise Mangler) to change a table. Sometimes EM
drops and recreates tables "under the covers" to accomplish a task that does
not have a corresponding direct T-SQL command. This can expand to multiple
tables when Referential Integrity constraints are involved. Most of the
time this doesn't cause a problem, but if the system is high volume or the
tables are large, it gac get very ugly, very quickly. Personally, I avoid
EM to do production table changes. I prefer to script everything and deploy
to a test/QA system first.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<quackhandle1975@.yahoo.co.uk> wrote in message
news:1123148974.573160.310190@.g47g2000cwa.googlegroups.com...
> Hi,
> Yesterday here we had an incident on one of our production servers
> where a few tables were explicitly dropped and recreated. I knew this
> because of the object create date and the table permissions had
> disappeared. This could have been a malicious attack or some process,
> however my hunch is with the former.
> Since the SQL Security here is going through a complete rethink (after
> the horse has bolted!), my question would be other than explicitly
> dropping and recreating a table is there a SQL Command/Process that
> does this? Also for future reference (in case this sort of thing
> happens again) I would like to setup server-side tracing, however I
> have noticed that the trace doesn't pick up a users/machines IP
> Address. I can see that in the SQL Error Logs use Network Address but
> can a trace explicitly pick up an IP? I know SQL Server can block
> certain IPs but can it log them?
> Any other ideas for preventing this sort of thing would be most
> welcome. Funny, you never think about security until you *REALLY* have
> to. A lesson to be learned here.
>
> Rgds,
> qh
>