Attach remote DB file: statements

Showing posts with label statements. Show all posts

Monday, March 19, 2012

auditting select statement

We have triggers written for insert/update/deletes of data, now there is a
new requirement to audit select statements ran against the database.
I know SQL Profiler shows the select statements ran. I was wondering if
anyone has suggestions on how to somehow use that function(or any other idea
)
and incorparate it into tracking all select statements?Tracey wrote:
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other id
ea)
> and incorparate it into tracking all select statements?
We monitor everything that happens in our production databases, by
running server-side traces 24x7 into rotating trace log files. Easily
done using sp_trace_create, etc., providing you have the disk space to
store the accumulating trace log files.
You get the added benefit of being able to analyze your database
activity to look for poorly performing queries.|||SQL Server does not track this activity. Profiler can see it because it
views all commands going into the database. Some options:
(1) If you deny SELECT access to tables and views, and force all access
through stored procedures, it is trivial to log this.
(2) You can have a trace running all the time that dumps data into trace
table(s).
(3) Or you can look at 3rd party tools (in which case, you won't have to
write all of the reporting over the trace table(s)). For example,
Lumigent's Audit DB or Log Explorer. See
http://www.aspfaq.com/search.asp?q=lumigent
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Tracey
Take a look at Dejan's example
For example, lets say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:
- SP:StmtCompleted and SQL: StmtCompleted events
- EventClass, TextData, ApplicationName and SPID columns
- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters
- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.
Start the trace, and create the following trigger using Query Analyzer:
CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
FOR INSERT
AS
EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning
Now check how trigger works by performing couple of selects:
SELECT TOP 1 *
FROM Customers
SELECT TOP 1 *
FROM Orders
SELECT TOP 1 c.CustomerID
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Hi Uri,
Seems good.. But is it fool -proof.
what will happen if I query this way.. just for an arguement
SELECT TOP 1 [Customers List].Customer_ID
FROM Orders as [Customers List]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Uri Dimant" wrote:

> Tracey
> Take a look at Dejan's example
> For example, let’s say we want to follow selects on the Customers table
of
> the Northwind database. Create a trace with only the following settings:
> - SP:StmtCompleted and SQL: StmtCompleted events
> - EventClass, TextData, ApplicationName and SPID columns
> - DatabaseID Equals 6 (DB_ID() of the Northwind database) and
> TextData Like select%customers% filters
> - Name the trace SelectTrigger and save it to a table with the sa
me
> name in the Northwind database.
> Start the trace, and create the following trigger using Query Analyzer:
>
> CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
> FOR INSERT
> AS
> EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
> warning
>
> Now check how trigger works by performing couple of selects:
>
> SELECT TOP 1 *
> FROM Customers
> SELECT TOP 1 *
> FROM Orders
> SELECT TOP 1 c.CustomerID
> FROM Customers c INNER JOIN Orders o
> ON c.CustomerID=o.CustomerID
>
> With Event Viewer, check whether you got two warnings in the Application l
og
> for the 1st and the 3rd queries (the 2nd should be filtered out).
>
> "Tracey" <Tracey@.discussions.microsoft.com> wrote in message
> news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
>
>|||Hi
This one will also be logged to the Apllication Viewer, however I agree that
this method is not perfect
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:499B5B18-A216-4732-BF8A-6B295C23D7D1@.microsoft.com...
> Hi Uri,
> Seems good.. But is it fool -proof.
> what will happen if I query this way.. just for an arguement
> SELECT TOP 1 [Customers List].Customer_ID
> FROM Orders as [Customers List]
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Uri Dimant" wrote:
>

Sunday, March 11, 2012

auditing SELECT statements

Is there a way to audit SELECT statements on a table? A
database has a few hundred tables and I was asked to find
the tables which are not used in the application. The same
question for UPDATE, DELETE and INSERT (it is not
desirable to use triggers to trace DML statements).
Thanks in advance, GregYou can use SQL Profiler. You use SQL Profiler to define your trace/audit
first. In the definition, you can filter for SELECT and/or the table name.
Then, you can generate a T-SQL script, using the Script Trace ... option
under the File menu, to run on the server side without any GUI frontend.
Check out the following articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;283790&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;283786&Product=sql2k
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:04f001c39e76$54347780$a401280a@.phx.gbl...
> Is there a way to audit SELECT statements on a table? A
> database has a few hundred tables and I was asked to find
> the tables which are not used in the application. The same
> question for UPDATE, DELETE and INSERT (it is not
> desirable to use triggers to trace DML statements).
> Thanks in advance, Greg|||Hmmm...I've never seen that ability before -- that's very cool!
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:e$hF6CpnDHA.1072@.TK2MSFTNGP09.phx.gbl...
> You can use SQL Profiler. You use SQL Profiler to define your trace/audit
> first. In the definition, you can filter for SELECT and/or the table name.
> Then, you can generate a T-SQL script, using the Script Trace ... option
> under the File menu, to run on the server side without any GUI frontend.
> Check out the following articles:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283790&Product=sql2k
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283786&Product=sql2k
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Greg" <anonymous@.discussions.microsoft.com> wrote in message
> news:04f001c39e76$54347780$a401280a@.phx.gbl...
> > Is there a way to audit SELECT statements on a table? A
> > database has a few hundred tables and I was asked to find
> > the tables which are not used in the application. The same
> > question for UPDATE, DELETE and INSERT (it is not
> > desirable to use triggers to trace DML statements).
> >
> > Thanks in advance, Greg
>|||If you have access to the source code of the application you can try
SQLClean by www.lockwoodtech.com . I haven't tried it myself though.
--
Jacco Schalkwijk
SQL Server MVP
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:04f001c39e76$54347780$a401280a@.phx.gbl...
> Is there a way to audit SELECT statements on a table? A
> database has a few hundred tables and I was asked to find
> the tables which are not used in the application. The same
> question for UPDATE, DELETE and INSERT (it is not
> desirable to use triggers to trace DML statements).
> Thanks in advance, Greg|||Linchi, is there a way to get ObjectName in the trace?
When i define a trace definition i can add ObjectName,
ObjectID or OwnerName but they are always empty. Is there
a way to populate them? Thanks for your help. Greg
>--Original Message--
>You can use SQL Profiler. You use SQL Profiler to define
your trace/audit
>first. In the definition, you can filter for SELECT
and/or the table name.
>Then, you can generate a T-SQL script, using the Script
Trace ... option
>under the File menu, to run on the server side without
any GUI frontend.
>Check out the following articles:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;283790&Product=sql2k
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;283786&Product=sql2k
>--
>Linchi Shea
>linchi_shea@.NOSPAMml.com
>
>"Greg" <anonymous@.discussions.microsoft.com> wrote in
message
>news:04f001c39e76$54347780$a401280a@.phx.gbl...
>> Is there a way to audit SELECT statements on a table? A
>> database has a few hundred tables and I was asked to
find
>> the tables which are not used in the application. The
same
>> question for UPDATE, DELETE and INSERT (it is not
>> desirable to use triggers to trace DML statements).
>> Thanks in advance, Greg
>
>.
>

Auditing Select Statements

Hello,
I need to find out if there is a way to audit selects against a certain
table. I know you can use triggers to monitor inserts, updates and
deletes but is there a way to monitor when someone selects specific
data? Thanks!
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Go to Lumigents web site... www.lumigent.com... I beleive they have an
auditing tool that does what you wish..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:eu0FLAVVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need to find out if there is a way to audit selects against a certain
> table. I know you can use triggers to monitor inserts, updates and
> deletes but is there a way to monitor when someone selects specific
> data? Thanks!
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Auditing Select Statements

Hello,
I need to find out if there is a way to audit selects against a certain
table. I know you can use triggers to monitor inserts, updates and
deletes but is there a way to monitor when someone selects specific
data? Thanks!
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Go to Lumigents web site... www.lumigent.com... I beleive they have an
auditing tool that does what you wish..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:eu0FLAVVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need to find out if there is a way to audit selects against a certain
> table. I know you can use triggers to monitor inserts, updates and
> deletes but is there a way to monitor when someone selects specific
> data? Thanks!
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Auditing Select Statements

Hello,
I need to find out if there is a way to audit selects against a certain
table. I know you can use triggers to monitor inserts, updates and
deletes but is there a way to monitor when someone selects specific
data? Thanks!
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Go to Lumigents web site... www.lumigent.com... I beleive they have an
auditing tool that does what you wish..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:eu0FLAVVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need to find out if there is a way to audit selects against a certain
> table. I know you can use triggers to monitor inserts, updates and
> deletes but is there a way to monitor when someone selects specific
> data? Thanks!
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Auditing DDL and schema changes in SQL Server 2000

Rather than reinvent the wheel, I was hoping that there was someone who has
already created a script allowing for the auditing of DDL statements, schema
changes within the database for SQL Server 2000.
I can easily capture the aforementioned in SQL 2005 by making use of a
trigger on
DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL 2000.
--
Thx,
Anthony E. CastroThere is no easy solution in 2000. If you really want this I recommend you
upgrade vs. spending the effort in 2000. But you can run a trace filtering
on only those type events but this is not a good long term solution. You can
also try one of the 3rd party tools to audit the tran logs but that has down
sides as well. Take a look at the free tool from www.red-gate.com for log
audits.
Andrew J. Kelly SQL MVP
"acmcdba68" <acmcdba68@.hotmail.com> wrote in message
news:8E8FE948-9D8A-4524-BF57-9EBA8CA9DF60@.microsoft.com...
> Rather than reinvent the wheel, I was hoping that there was someone who
> has
> already created a script allowing for the auditing of DDL statements,
> schema
> changes within the database for SQL Server 2000.
> I can easily capture the aforementioned in SQL 2005 by making use of a
> trigger on
> DDL_DATABASE_LEVEL_EVENTS... What I want, is to do exactly this in SQL
> 2000.
> --
> Thx,
> Anthony E. Castro

Auditing DDL and schema changes in SQL Server 2000

Thursday, March 8, 2012

Audit tables ?

I cant find feature to audit executin of statements on table level (I read somewhere that is one of new feature of SQL 2008). For an exemple to now who is make an UPDATE, INSERT or DELETE statements.

In Katmai we have the cdc(Change data Capture).Change data capture(CDC) is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

For more details go to books online and write "change data capture"....

|||We do plan to provide auditing support in SQL Server 2008. However, the feature is not available in SQL Server 2008 June CTP. Expect to see it in a future CTP.|||

Sara Tahir [MSFT] wrote:
We do plan to provide auditing support in SQL Server 2008. However, the feature is not available in SQL Server 2008 June CTP. Expect to see it in a future CTP.

I just tested cdc and it works fine in the june ctp.

|||

Yes, CDC works just fine. There is couple of steps to make ti work...but it's cool feature.

Example:

--to activate cdc

EXEC sys.sp_cdc_enable_db_change_data_capture

--checking

SELECT is_cdc_enabled FROM sys.databases WHERE name = 'databaseName'

--enabling chanage data capture on specific table

USE test;

EXECUTE sys.sp_cdc_enable_table_change_data_capture
@.source_schema = N'dbo'
, @.source_name = N'Imena'
, @.role_name = N'cdc_Admin';

-- INSERT, DELETE, UPDATE some staff in table

-- look at change history

SELECT * FROM cdc.dbo_tableName_CT

Also you can trakc DDL statments in ddl_history table

It would be nice to have user name in cdc.dbo_tableName_CT

|||

What is the performance impact of CDC?

Can you write the data to audit detail to another database?

-John

|||

Hi all,

I have also worked a little on the CDC. You can find my notes at http://www.kodyaz.com/articles/change-data-capture.aspx

As far as I see, there is not any information about who did the change kept on the CDC tables, on mirror of the original tracked table. Or am I missing something since I did not check more on the underlying tables.

CDC is a common topic all of us dealing with. I guess you have followed the topic "Data Auditing vs CDC" at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1730222&SiteID=1 also topic "CDC" at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1752141&SiteID=1

In those forms, CDC is said to be designed for ETL and also data is extracted from transaction logs. Additional disk activity is the major topic that may affect the performance. But it should be handled in each databases own conditions.

I do not think it is possible to configure the CDC tables to be created on an other database.

|||

eralper wrote:

As far as I see, there is not any information about who did the change kept on the CDC tables, on mirror of the original tracked table. Or am I missing something since I did not check more on the underlying tables.

A lot of DBA and programmers will use CDC to kept record of changaed data, but withou who part, its a little bit poor feature.

|||

Since I only have Katmai running on a MacBook Pro running Vista in Parallels Desktop, and on a lowly server in our data center, I have not had the luxury of doing extensive performance tests just yet. Simon Sabin raised an issue on connect about the expense of turning a bulk update into a set of 1000's of insert statements (to the tune of 2 * the number of rows affected), which I can understand would take over certain systems.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283761

As for writing the data to another database, you don't have any control over the destination CDC objects, but sure, you can create a trigger against them. I coded up a very basic example which (thouh it stored the data in the same DB, it doesn't have to) augmented the data collection with username and date/time, which CDC doesn't collect:

http://sqlblog.com/blogs/aaron_bertrand/archive/2007/06/21/playing-with-cdc-in-katmai.aspx

I asked for more options here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283707

Note that there are currently a couple of bugs, for example, modifying the base table in SSMS will eradicate the CDC objects on it without warning, and modifying the table using ALTER TABLE does not change the set of columns that are captured. If you drop a column, it stays in the capture table but all values become NULL; if you add a column, it does not appear in the capture table unloess you drop the CDC config and re-create it. I did not test what happens when you rename a column or change its datatype.

These are documented here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283765

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283442

Hope this information is helpful.

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

|||Sorry, this was in response to:

What is the performance impact of CDC?

Can you write the data to audit detail to another database?
>>

These forums don't handle threading very well, do they?

|||

CDC will have performance impact. a form of replication's logreader agent will scan the transaction log of the database ( can consume CPU as well as disk activity) and will write to the change tables. So for every ins/upd/del you want caputred will incur an additional write to disk. Since disk can be a bottleneck there are a couple ways to alleviate this - the capture scan job can be scheduled to run at off peak hours, and you can also place the capture tables on a different db filegroup, but it has to be the same filegroup as the db.

|||

One more note - as mentioned above, CDC was not designed for auditing, so it most likely won't meet your auditing needs.

|||

As Greg has already mentioned, CDC is not designed for auditing (as traditionally defined) puposes. If it meets your needs, then that is great. As I've already posted earlier in this thread, we do plan to provide auditing support in SQL Server 2008. However, the feature is not available in SQL Server 2008 July CTP. I'd highly recommend trying out the auditing functionality when it becomes available in a future CTP.

For more on CDC vs. Auditing, take a look at the following thread on this forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1730222&SiteID=1

Audit tables ?

I cant find feature to audit executin of statements on table level (I read somewhere that is one of new feature of SQL 2008). For an exemple to now who is make an UPDATE, INSERT or DELETE statements.

For more details go to books online and write "change data capture"....

|||We do plan to provide auditing support in SQL Server 2008. However, the feature is not available in SQL Server 2008 June CTP. Expect to see it in a future CTP.|||

Sara Tahir [MSFT] wrote:
We do plan to provide auditing support in SQL Server 2008. However, the feature is not available in SQL Server 2008 June CTP. Expect to see it in a future CTP.

I just tested cdc and it works fine in the june ctp.

|||

Yes, CDC works just fine. There is couple of steps to make ti work...but it's cool feature.

Example:

--to activate cdc

EXEC sys.sp_cdc_enable_db_change_data_capture

--checking

SELECT is_cdc_enabled FROM sys.databases WHERE name = 'databaseName'

--enabling chanage data capture on specific table

USE test;

EXECUTE sys.sp_cdc_enable_table_change_data_capture
@.source_schema = N'dbo'
, @.source_name = N'Imena'
, @.role_name = N'cdc_Admin';

-- INSERT, DELETE, UPDATE some staff in table

-- look at change history

SELECT * FROM cdc.dbo_tableName_CT

Also you can trakc DDL statments in ddl_history table

It would be nice to have user name in cdc.dbo_tableName_CT

|||

What is the performance impact of CDC?

Can you write the data to audit detail to another database?

-John

|||

Hi all,

I have also worked a little on the CDC. You can find my notes at http://www.kodyaz.com/articles/change-data-capture.aspx

I do not think it is possible to configure the CDC tables to be created on an other database.

|||

eralper wrote:

As far as I see, there is not any information about who did the change kept on the CDC tables, on mirror of the original tracked table. Or am I missing something since I did not check more on the underlying tables.

A lot of DBA and programmers will use CDC to kept record of changaed data, but withou who part, its a little bit poor feature.

|||

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283761

http://sqlblog.com/blogs/aaron_bertrand/archive/2007/06/21/playing-with-cdc-in-katmai.aspx

I asked for more options here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283707

These are documented here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283765

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283442

Hope this information is helpful.

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

|||Sorry, this was in response to:

What is the performance impact of CDC?

Can you write the data to audit detail to another database?
>>

These forums don't handle threading very well, do they?

|||

One more note - as mentioned above, CDC was not designed for auditing, so it most likely won't meet your auditing needs.

|||

For more on CDC vs. Auditing, take a look at the following thread on this forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1730222&SiteID=1

Audit tables ?

I cant find feature to audit executin of statements on table level (I read somewhere that is one of new feature of SQL 2008). For an exemple to now who is make an UPDATE, INSERT or DELETE statements.

For more details go to books online and write "change data capture"....

|||We do plan to provide auditing support in SQL Server 2008. However, the feature is not available in SQL Server 2008 June CTP. Expect to see it in a future CTP.|||

Sara Tahir [MSFT] wrote:
We do plan to provide auditing support in SQL Server 2008. However, the feature is not available in SQL Server 2008 June CTP. Expect to see it in a future CTP.

I just tested cdc and it works fine in the june ctp.

|||

Yes, CDC works just fine. There is couple of steps to make ti work...but it's cool feature.

Example:

--to activate cdc

EXEC sys.sp_cdc_enable_db_change_data_capture

--checking

SELECT is_cdc_enabled FROM sys.databases WHERE name = 'databaseName'

--enabling chanage data capture on specific table

USE test;

EXECUTE sys.sp_cdc_enable_table_change_data_capture
@.source_schema = N'dbo'
, @.source_name = N'Imena'
, @.role_name = N'cdc_Admin';

-- INSERT, DELETE, UPDATE some staff in table

-- look at change history

SELECT * FROM cdc.dbo_tableName_CT

Also you can trakc DDL statments in ddl_history table

It would be nice to have user name in cdc.dbo_tableName_CT

|||

What is the performance impact of CDC?

Can you write the data to audit detail to another database?

-John

|||

Hi all,

I have also worked a little on the CDC. You can find my notes at http://www.kodyaz.com/articles/change-data-capture.aspx

I do not think it is possible to configure the CDC tables to be created on an other database.

|||

eralper wrote:

As far as I see, there is not any information about who did the change kept on the CDC tables, on mirror of the original tracked table. Or am I missing something since I did not check more on the underlying tables.

A lot of DBA and programmers will use CDC to kept record of changaed data, but withou who part, its a little bit poor feature.

|||

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283761

http://sqlblog.com/blogs/aaron_bertrand/archive/2007/06/21/playing-with-cdc-in-katmai.aspx

I asked for more options here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283707

These are documented here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283765

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=283442

Hope this information is helpful.

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

|||Sorry, this was in response to:

What is the performance impact of CDC?

Can you write the data to audit detail to another database?
>>

These forums don't handle threading very well, do they?

|||

One more note - as mentioned above, CDC was not designed for auditing, so it most likely won't meet your auditing needs.

|||

For more on CDC vs. Auditing, take a look at the following thread on this forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1730222&SiteID=1

Saturday, February 25, 2012

Audit Delete Statements

Hi

I was curious whether it's possible to audit DELETE statements in the MS SQL database. I created a procedure (below), but I didn't find any event associated with DELETE statements.

Any help will be greatly appreciated!

Thanks,
Alla

CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/

-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
declare @.StatusMsg varchar
declare @.ServerTraceFile varchar
set @.ServerTraceFile = 'E:\Program Files\Microsoft SQL Server\MSSQL\Trace\Audit_Info'
set @.maxfilesize = 1024

-- Client side File and Table cannot be scripted

-- Set the events
declare @.on bit
set @.on = 1

exec @.rc = sp_trace_create @.TraceID OUTPUT, 0, N'\\hostname\dbauditlog\my_dir', @.maxfilesize, NULL
print @.TraceID

if (@.rc != 0) goto error
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint

exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
--SELECT @.StatusMsg = 'sp_trace_setstatus' + ' Error - ' + @.TraceID
-- display trace id for future references
select TraceID=@.TraceID

goto noCursor

error:
select ErrorCode=@.rc

noCursor:
return

GO
exec sp_procoption N'sp_Turn_Audit_On', N'startup', N'true'
GOTake a look at tirggers (http://doc.ddart.net/mssql/sql70/create_8.htm) :)|||I see...

I just noticed that there is an event to audit TSQL via SQL Profiler. I spooled the script to a SQL file. however, is there any way of filtering it that it would capture DELETE statements only?

Thanks,
Alla|||Spool SQL Profiler output to a table so you can query it.

Friday, February 24, 2012

Attempting to restore a db from a network drive and use the 'with move' statement

Attempting to restore a db from a network drive and need to move the
log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
Here is the script that I am using for this procedure
===================================== USE MASTER
GO
RESTORE FILELISTONLY
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
RESTORE LOG Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
-- WITH NORECOVERY
RESTORE DATABASE Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH NORECOVERY
=====================================
Here are the error(s) that I am receiving
===================================== Server: Msg 913, Level 16, State 8, Line 3
Could not find database ID 65535. Database may not be activated yet or
may be in transition.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Server: Msg 5105, Level 16, State 2, Line 7
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
incorrect.
Server: Msg 3156, Level 16, State 1, Line 7
File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.war_wheelan@.yahoo.com wrote:
> Attempting to restore a db from a network drive and need to move the
> log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
> AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
> Here is the script that I am using for this procedure
> =====================================> USE MASTER
> GO
> RESTORE FILELISTONLY
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> RESTORE LOG Indii
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
> -- WITH NORECOVERY
> RESTORE DATABASE Indii
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> WITH NORECOVERY
> =====================================> Here are the error(s) that I am receiving
> =====================================> Server: Msg 913, Level 16, State 8, Line 3
> Could not find database ID 65535. Database may not be activated yet or
> may be in transition.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> Server: Msg 5105, Level 16, State 2, Line 7
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
> incorrect.
> Server: Msg 3156, Level 16, State 1, Line 7
> File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
> a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE DATABASE is terminating abnormally.
>
It looks like you're trying to restore the log before the database has
been restored. Restore the database first, THEN the log.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
The reason that I attempted to restore the log file first is because I
receive the same error either way.
Tracy McKibben wrote:
> war_wheelan@.yahoo.com wrote:
> > Attempting to restore a db from a network drive and need to move the
> > log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
> > AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
> >
> > Here is the script that I am using for this procedure
> > =====================================> > USE MASTER
> > GO
> > RESTORE FILELISTONLY
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > RESTORE LOG Indii
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
> > -- WITH NORECOVERY
> > RESTORE DATABASE Indii
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > WITH NORECOVERY
> > =====================================> >
> > Here are the error(s) that I am receiving
> > =====================================> > Server: Msg 913, Level 16, State 8, Line 3
> > Could not find database ID 65535. Database may not be activated yet or
> > may be in transition.
> > Server: Msg 3013, Level 16, State 1, Line 3
> > RESTORE LOG is terminating abnormally.
> > Server: Msg 5105, Level 16, State 2, Line 7
> > Device activation error. The physical file name 'C:\Program
> > Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
> > incorrect.
> > Server: Msg 3156, Level 16, State 1, Line 7
> > File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
> > a valid location for the file.
> > Server: Msg 3013, Level 16, State 1, Line 7
> > RESTORE DATABASE is terminating abnormally.
> >
> It looks like you're trying to restore the log before the database has
> been restored. Restore the database first, THEN the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Monday, March 19, 2012

auditting select statement

Sunday, March 11, 2012

auditing SELECT statements

Auditing Select Statements

Auditing Select Statements

Auditing Select Statements

Auditing DDL and schema changes in SQL Server 2000

Auditing DDL and schema changes in SQL Server 2000

Auditing DDL and schema changes in SQL Server 2000

Auditing DDL and schema changes in SQL Server 2000

Thursday, March 8, 2012

Audit tables ?

Audit tables ?

Audit tables ?

Saturday, February 25, 2012

Audit Delete Statements

Friday, February 24, 2012

Attempting to restore a db from a network drive and use the 'with move' statement

Attach remote DB file

Blog Archive

About Me