Auditing SQL?

Hello, My company wants to track activity on SQL that
happens outside of the Applications that use it. For
instance if someone modifies a table in someway (Query
Analyzer, ASP, vbscript) without using the Application
that is intended to do it. Could someone point me in the
right direction on enabling this on SQL, or recommend a
product.
ThanksStart a Profiler Trace and add the valid app names to the filter on
ApplicationName NOT Like...
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rog" <anonymous@.discussions.microsoft.com> wrote in message
news:9a6c01c405f8$cfc8d410$a401280a@.phx.gbl...
Hello, My company wants to track activity on SQL that
happens outside of the Applications that use it. For
instance if someone modifies a table in someway (Query
Analyzer, ASP, vbscript) without using the Application
that is intended to do it. Could someone point me in the
right direction on enabling this on SQL, or recommend a
product.
Thanks|||You might look at Lumigent's tool called Entegra. www.lumigent.com
Ray Higdon MCSE, MCDBA, CCNA
--
"Rog" <anonymous@.discussions.microsoft.com> wrote in message
news:9a6c01c405f8$cfc8d410$a401280a@.phx.gbl...
> Hello, My company wants to track activity on SQL that
> happens outside of the Applications that use it. For
> instance if someone modifies a table in someway (Query
> Analyzer, ASP, vbscript) without using the Application
> that is intended to do it. Could someone point me in the
> right direction on enabling this on SQL, or recommend a
> product.
> Thanks

Audit log that SA cannot modify

Anyone familiar with ways (SQL Server 2000, or 2005) to have an audit
log of who does what in the database (outside of a Great Plains front
end passing over requests) - that the SA cannot modify? If the SA - or
anyone - can modify the log - its no good from an audit perspective.
It has to be read-only. Any software packages out there that also do
this and present the log in a form thats easy to query / review?
Thanks!
Jason ShohetHi
In SQL Server 2005 you will be able to define a trigger on database level to
capture events.
<jasonshohet@.gmail.com> wrote in message
news:1139999866.893906.89260@.z14g2000cwz.googlegroups.com...
> Anyone familiar with ways (SQL Server 2000, or 2005) to have an audit
> log of who does what in the database (outside of a Great Plains front
> end passing over requests) - that the SA cannot modify? If the SA - or
> anyone - can modify the log - its no good from an audit perspective.
> It has to be read-only. Any software packages out there that also do
> this and present the log in a form thats easy to query / review?
> Thanks!
> Jason Shohet
>|||But the SA can disable the trigger, thats not enough.
I want something that can audit the SA himself - and anyone else. It
should report on all schema changes and all transactions made to the db
- by anyone - and nobody should be able to modify it (including the SA)
except truncate the log by date range at the end of the audit period.|||Hi
Don't you trust in SA? :-))))))
Remove people that you don't want from sysadmin server role and then you
audit them by using triggers
<jasonshohet@.gmail.com> wrote in message
news:1140012486.489277.187450@.g14g2000cwa.googlegroups.com...
> But the SA can disable the trigger, thats not enough.
> I want something that can audit the SA himself - and anyone else. It
> should report on all schema changes and all transactions made to the db
> - by anyone - and nobody should be able to modify it (including the SA)
> except truncate the log by date range at the end of the audit period.
>|||Ha, the issue is that the SA needs to be able to do this himself
but the SA role is necessary to perform maintenance on the SQL Server I
assume. Isn't there something that a QA person can install with the SA
watching perhaps - eg. a 3rd party logger, that can audit all
activities, that the SA cannot interfere with once installed. Pie in
the sky?|||Not pie in the sky. You can look at options with SQL Server
such as server side traces, maybe c2 auditing. Lots of third
party products that monitor activity - more products now
with SOX requirements. A couple of many would be AuditDB
from Lumigent: http://www.lumigent.com/products/auditdb.html
and Compliance Manager from Idera:
http://www.idera.com/Products/SQLcm/
-Sue
On 15 Feb 2006 14:15:29 -0800, jasonshohet@.gmail.com wrote:

>Ha, the issue is that the SA needs to be able to do this himself
>but the SA role is necessary to perform maintenance on the SQL Server I
>assume. Isn't there something that a QA person can install with the SA
>watching perhaps - eg. a 3rd party logger, that can audit all
>activities, that the SA cannot interfere with once installed. Pie in
>the sky?

Attaching Incremental data

I have a blank SQL database that will be generated with tables transferred
from an outside source. Once the tables are there, on a daily basis,
incremental data will be sent to an FTP folder in order attach them to the
original tables. What I need to know is how to attach the incremental data t
o
the tables. I know i'll probably schedule a job to run DTS to transform the
data from the FTP folder to the SQL server, then another job to attach the
incremental data once in SQL server to the tables. I need help with the
stored procedure I should use to attach the incremental data.
Thanks
TSTS,
Attach? I think you mean import. Anyway if there are not a ton of records
and you require some data manipulation then DTS would be the way to go. And
you're right scheduling the DTS package as a job will help automate the
import. You'll probably only need one DTS package/job to make this work.
As for the stored procedure help...that's pretty much impossible without
knowing your table structure, data, file format, etc... Please provide us
with more information as you get closer to implementing this solution.
HTH
Jerry
"TS" <TS@.discussions.microsoft.com> wrote in message
news:36C4AC57-E243-4FB1-BDE1-F1A3E3EE06A5@.microsoft.com...
>I have a blank SQL database that will be generated with tables transferred
> from an outside source. Once the tables are there, on a daily basis,
> incremental data will be sent to an FTP folder in order attach them to the
> original tables. What I need to know is how to attach the incremental data
> to
> the tables. I know i'll probably schedule a job to run DTS to transform
> the
> data from the FTP folder to the SQL server, then another job to attach the
> incremental data once in SQL server to the tables. I need help with the
> stored procedure I should use to attach the incremental data.
> Thanks
> --
> TS|||Refer to the post from earlier today titled: "Efficiently Inserting 1
Million records".
"TS" <TS@.discussions.microsoft.com> wrote in message
news:36C4AC57-E243-4FB1-BDE1-F1A3E3EE06A5@.microsoft.com...
>I have a blank SQL database that will be generated with tables transferred
> from an outside source. Once the tables are there, on a daily basis,
> incremental data will be sent to an FTP folder in order attach them to the
> original tables. What I need to know is how to attach the incremental data
> to
> the tables. I know i'll probably schedule a job to run DTS to transform
> the
> data from the FTP folder to the SQL server, then another job to attach the
> incremental data once in SQL server to the tables. I need help with the
> stored procedure I should use to attach the incremental data.
> Thanks
> --
> TS|||Thanks for your response. I'm just curious and I want to make some researche
s
from my side on how to import incremntal data to tables? If you can give me
a
general idea on how to do this or tell me if I can find any documentation on
this.
Thanks
--
TS
"Jerry Spivey" wrote:

> TS,
> Attach? I think you mean import. Anyway if there are not a ton of record
s
> and you require some data manipulation then DTS would be the way to go. A
nd
> you're right scheduling the DTS package as a job will help automate the
> import. You'll probably only need one DTS package/job to make this work.
> As for the stored procedure help...that's pretty much impossible without
> knowing your table structure, data, file format, etc... Please provide us
> with more information as you get closer to implementing this solution.
> HTH
> Jerry
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:36C4AC57-E243-4FB1-BDE1-F1A3E3EE06A5@.microsoft.com...
>
>