Drop table

Hi NG,
We have a development database and from time to time some user or users will
issue a drop table and I would like to know the user who drop the table.
Is there a way to determine who and when the user issues the drop table DDL
without running the profiler?
If it can't be done without using profiler, how do I filter it by drop table
only if I'm running the profiler?
If you know of any third party software please include it also in your
reply.
Thank you in advance.Hi,
Which version of SQL are you using?
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:e7v%23IdJ$FHA.3676@.tk2msftngp13.phx.gbl...
> Hi NG,
> We have a development database and from time to time some user or users
> will
> issue a drop table and I would like to know the user who drop the table.
> Is there a way to determine who and when the user issues the drop table
> DDL
> without running the profiler?
> If it can't be done without using profiler, how do I filter it by drop
> table
> only if I'm running the profiler?
> If you know of any third party software please include it also in your
> reply.
> Thank you in advance.
>|||SQL Server 2000
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:uQbJcWK$FHA.2740@.tk2msftngp13.phx.gbl...
> Hi,
> Which version of SQL are you using?
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
> news:e7v%23IdJ$FHA.3676@.tk2msftngp13.phx.gbl...
> > Hi NG,
> >
> > We have a development database and from time to time some user or users
> > will
> > issue a drop table and I would like to know the user who drop the table.
> >
> > Is there a way to determine who and when the user issues the drop table
> > DDL
> > without running the profiler?
> >
> > If it can't be done without using profiler, how do I filter it by drop
> > table
> > only if I'm running the profiler?
> >
> > If you know of any third party software please include it also in your
> > reply.
> >
> > Thank you in advance.
> >
> >
>|||Hi,
You should use profiler in this case. In SQL 2005 there are DDL triggers you
could use.
For filtering you could filter on Textdata data column with %DROP TABLE% as
a filter. This way you'll see just DROPs of tables and of course you should
include LoginName or DBUserName data columns to see which user issued ta
command.
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
news:eNhYQhK$FHA.2944@.TK2MSFTNGP10.phx.gbl...
> SQL Server 2000
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:uQbJcWK$FHA.2740@.tk2msftngp13.phx.gbl...
>> Hi,
>> Which version of SQL are you using?
>> --
>> Danijel Novak
>> MCP+I, MCSA, MCSE, MCDBA, MCT
>>
>> "Praetorian Guard" <praetorian@.gatekeeper.com> wrote in message
>> news:e7v%23IdJ$FHA.3676@.tk2msftngp13.phx.gbl...
>> > Hi NG,
>> >
>> > We have a development database and from time to time some user or users
>> > will
>> > issue a drop table and I would like to know the user who drop the
>> > table.
>> >
>> > Is there a way to determine who and when the user issues the drop table
>> > DDL
>> > without running the profiler?
>> >
>> > If it can't be done without using profiler, how do I filter it by drop
>> > table
>> > only if I'm running the profiler?
>> >
>> > If you know of any third party software please include it also in your
>> > reply.
>> >
>> > Thank you in advance.
>> >
>> >
>>
>|||"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:e83wq9L$FHA.3464@.TK2MSFTNGP15.phx.gbl...
> Hi,
> You should use profiler in this case. In SQL 2005 there are DDL triggers
> you could use.
In 2005 you don't even need DDL triggers. The lightweight always-on trace
will catch this, and the "Schema Changes History" report in Management
Studio will show you who did what.
Now, DDL triggers could be used to capture more details, or actually prevent
the table from being dropped...
David|||I believe that is what the other reference was for. Just know the small
trace is not a permanent log, it rolls over.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uTGN%23BN$FHA.504@.TK2MSFTNGP12.phx.gbl...
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:e83wq9L$FHA.3464@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> You should use profiler in this case. In SQL 2005 there are DDL triggers
>> you could use.
> In 2005 you don't even need DDL triggers. The lightweight always-on trace
> will catch this, and the "Schema Changes History" report in Management
> Studio will show you who did what.
> Now, DDL triggers could be used to capture more details, or actually
> prevent the table from being dropped...
> David
>|||Hi Andy,
Do you know any method to automatically save the contents of that trace file?
Something like a sheduled task or SQL Job?
Thanx,
Sorin
"Andy Wilbourn" wrote:
> I believe that is what the other reference was for. Just know the small
> trace is not a permanent log, it rolls over.
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uTGN%23BN$FHA.504@.TK2MSFTNGP12.phx.gbl...
> >
> > "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> > news:e83wq9L$FHA.3464@.TK2MSFTNGP15.phx.gbl...
> >> Hi,
> >>
> >> You should use profiler in this case. In SQL 2005 there are DDL triggers
> >> you could use.
> >
> > In 2005 you don't even need DDL triggers. The lightweight always-on trace
> > will catch this, and the "Schema Changes History" report in Management
> > Studio will show you who did what.
> >
> > Now, DDL triggers could be used to capture more details, or actually
> > prevent the table from being dropped...
> >
> > David
> >
>
>