How to Audit DMLs in SQL Server 2005

Hi,

I am looking for solutions of auditing DMLs using SQL Server 2005.
The tasks are:

1) Audit DML statements, including SELECT, INSERT, UPDATE, and DELETE. Please note, not only UPDATE, DELETE and INSERT, but also SELECT
2) Protect the audit trails from insider threats, i.e. protect the audit trails from DBAs.

The questions are:
What would be the solutions recommended by MS?
What are the options, either good or bad, to accomplish auditing DML and protecting audit trails.

Thanks a lot

The most 'robust' and secure method is utilzing one of the third party products designed for that purpose. Any method utilizing server based triggers or stored procedures 'could' be vunerable to DBA mis-use.

Here are links to most of them -there could be additional not on my list.

Audit Tools
ApexSQL Audit http://www.apexsql.com/sql_tools_audit.asp
AuditDatabase (Free Web based trigger generation) http://www.auditdatabase.com/
Lumigent Adit DB http://www.lumigent.com/products/auditdb.html
OmniAudit http://www.krell-software.com/omniaudit/index.asp
SQLLog http://www.rlpsoftware.com/mainframe.asp?contents=SQLLog.asp&mainmenu=SQLLog&submenu=Info
Upscene SQL Log Manager http://www.upscene.com/index.htm?./products/audit/mssqllm_main.htm
DB Audit Expert http://www.softtreetech.com/dbaudit/

|||Hi, Arnie,

Thanks a lot for your prompt response.

I checked out these products and found they can audit INSERT, UPDATE, and DELETE, most via triggers, but they can not audit SELECT.

Please forgive me for a few more questions in that direction:

1) Is there any built-in features of SQL Server 2005 for DML auditing, except database triggers?
2) Can SQL Profiler be used for auditing SELECT, INSERT, UPDATE and DELETE statements?
3) Is there any way to audit SELECT using SQL Server 2005?

Thanks a lot again :-)

|||

I don't think that is exactly correct. Lunigent's Audit tool reads the Transaction log -does not rely on database Triggers, AND can easily track SELECT activity.

You should check it out a bit more. Not inexpensive, but it will be less than the time cost to attempt to create anything that does the job, and whatever is custom created is guaranteed to be less 'robust'.

http://lumigent.com/products/auditdb.html

Your questions...

1. No

2. Yes, but with a measurable performance hit. And would be easily accessible by a DBA since it requires the same level of access to the server as a DBA.

3. No, not directly. SELECT statement 'could' be done through stored procedures, and INSERTs make to a logging table, but again, suseptible to DBA interference.

If you want to be able to log priviledged users (DBA, DBO, etc.) AND data reads, I don't know of any 'secure' method other than the third party products.

|||That is great to know that SQL Profiler can audit SELECT, INSERT,UPDATE and DELETE.

Assuming we ignore the requirement about protecting auditing trails for this moment:

1) Which event(s) should I audit SELECT, UPDATE, DELETE and INSERT against a table using SQL Profiler?
2) Can we audit the (before and after) values of UPDATE, DELETE and INSERT on a table in a trace log?
3) Also I am a little confused: while saying SQL Server 2005 doesn't have built-in auditing DML feature (except triggers), however, SQL Profiler does audit SELECT, UPDATE, DELETE and INSERT. Is there any thing wrong in auditing DML in SQL Profiler, except performance issue? I am not sure if we could say or use SQL Profiler to audit DML.

Yes, I double checked Lunigent's Audit tool, it does audit all DMLs.
Thanks a lot for that information.

I am very new to SQL Server, please forgive me for asking so much questions

|||

Profiler can capture the command and parameters/values that were sent to SQL Server. It does not, however, provide the results (or 'after) state due to data modification -UNLESS there is another specific data read.

Events: TSQL: StmtStarting, TSQL: StmtCompleting

Profiler wasn't designed as a 'Auditing' tool.

Profiler is a bit 'ham handed' compared to the tools specifically designed for auditing.

Profiler requires the same level of permissions as a DBA would have.

Profiler can be easily 'thwarted' by a DBA.

AS you stated your specifications, I highly recommend NOT going further with Profiler for the Auditing task.

You will be investing a lot of time and effort, and it still will not meet your requirements.

|||Thank you very much, Arnie, your comments are very helpful...
sql

How to assign values to a variable from a xls sheet?

I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?

select Proyecto from [Carga$]

TIA,

I'm so sorry it's solved!!

I promise you that from now on I'll try not be so impatient..

how to append 2 fields together

Hi I am inserting some data into a temp @.table and trying to combine two
different fields with the + character so in the insert statement I have,
Case when E.ColorID IS NOT Null then TE.equipmentName +
(Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
seems to result in an error when running the query.
string or binary data would be truncated!
If I remove the TE.equipment_Type_Name_VC it runs fine.
I am trying to combine the TE.equipmentName and Color
thanks
Paul G
Software engineer.
Paul,
Your concatenated strings are too long for the column declaration in your
temp @.table. A couple of ways to fix it are:
1. Increase the size of the column
2. Trim the text of the concatenation. For example, assuming (perhaps
incorrectly) that equipmentName and Color are both fixed length columns and
that you would like a space between the strings, you might use something
like:
LTRIM(LTRIM(Te.equipmentName) + ' ' + (Select [Color] from Color Where
ID = E.Color_ID))
I see that you are doing a subselect to get [Color] inside the case, but
this is probably not necessary. If you are only doing this to avoid
problems where there is no usable ColorID, then perhaps.
SELECT LTRIM(LTRIM(Te.equipmentName) + ' ' + COALESCE([Color],''))
FROM TechEquipment Te LEFT OUTER JOIN Color C
ON Te.ColorID = C.ID
The sample code aliases don't make sense to me, so this above is just an
outline. Be sure to plug in your tables and alias properly, etc.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4548E03A-B87F-43BE-ABDD-05C6766A4565@.microsoft.com...
> Hi I am inserting some data into a temp @.table and trying to combine two
> different fields with the + character so in the insert statement I have,
> Case when E.ColorID IS NOT Null then TE.equipmentName +
> (Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
>
> seems to result in an error when running the query.
> string or binary data would be truncated!
> If I remove the TE.equipment_Type_Name_VC it runs fine.
> I am trying to combine the TE.equipmentName and Color
> thanks
> --
> Paul G
> Software engineer.
|||Hi Russell, thanks for the response. I fixed it about 5 minutes ago, had to
increase the size on the column of the results table, one of the solutions
you specified!
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> Your concatenated strings are too long for the column declaration in your
> temp @.table. A couple of ways to fix it are:
> 1. Increase the size of the column
> 2. Trim the text of the concatenation. For example, assuming (perhaps
> incorrectly) that equipmentName and Color are both fixed length columns and
> that you would like a space between the strings, you might use something
> like:
> LTRIM(LTRIM(Te.equipmentName) + ' ' + (Select [Color] from Color Where
> ID = E.Color_ID))
> I see that you are doing a subselect to get [Color] inside the case, but
> this is probably not necessary. If you are only doing this to avoid
> problems where there is no usable ColorID, then perhaps.
> SELECT LTRIM(LTRIM(Te.equipmentName) + ' ' + COALESCE([Color],''))
> FROM TechEquipment Te LEFT OUTER JOIN Color C
> ON Te.ColorID = C.ID
> The sample code aliases don't make sense to me, so this above is just an
> outline. Be sure to plug in your tables and alias properly, etc.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4548E03A-B87F-43BE-ABDD-05C6766A4565@.microsoft.com...
>
>

how to append 2 fields together

Hi I am inserting some data into a temp @.table and trying to combine two
different fields with the + character so in the insert statement I have,
Case when E.ColorID IS NOT Null then TE.equipmentName +
(Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
seems to result in an error when running the query.
string or binary data would be truncated!
If I remove the TE.equipment_Type_Name_VC it runs fine.
I am trying to combine the TE.equipmentName and Color
thanks
--
Paul G
Software engineer.Paul,
Your concatenated strings are too long for the column declaration in your
temp @.table. A couple of ways to fix it are:
1. Increase the size of the column
2. Trim the text of the concatenation. For example, assuming (perhaps
incorrectly) that equipmentName and Color are both fixed length columns and
that you would like a space between the strings, you might use something
like:
LTRIM(LTRIM(Te.equipmentName) + ' ' + (Select [Color] from Color Where
ID = E.Color_ID))
I see that you are doing a subselect to get [Color] inside the case, but
this is probably not necessary. If you are only doing this to avoid
problems where there is no usable ColorID, then perhaps.
SELECT LTRIM(LTRIM(Te.equipmentName) + ' ' + COALESCE([Color],''))
FROM TechEquipment Te LEFT OUTER JOIN Color C
ON Te.ColorID = C.ID
The sample code aliases don't make sense to me, so this above is just an
outline. Be sure to plug in your tables and alias properly, etc.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4548E03A-B87F-43BE-ABDD-05C6766A4565@.microsoft.com...
> Hi I am inserting some data into a temp @.table and trying to combine two
> different fields with the + character so in the insert statement I have,
> Case when E.ColorID IS NOT Null then TE.equipmentName +
> (Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
>
> seems to result in an error when running the query.
> string or binary data would be truncated!
> If I remove the TE.equipment_Type_Name_VC it runs fine.
> I am trying to combine the TE.equipmentName and Color
> thanks
> --
> Paul G
> Software engineer.|||Hi Russell, thanks for the response. I fixed it about 5 minutes ago, had to
increase the size on the column of the results table, one of the solutions
you specified!
--
Paul G
Software engineer.
"Russell Fields" wrote:
> Paul,
> Your concatenated strings are too long for the column declaration in your
> temp @.table. A couple of ways to fix it are:
> 1. Increase the size of the column
> 2. Trim the text of the concatenation. For example, assuming (perhaps
> incorrectly) that equipmentName and Color are both fixed length columns and
> that you would like a space between the strings, you might use something
> like:
> LTRIM(LTRIM(Te.equipmentName) + ' ' + (Select [Color] from Color Where
> ID = E.Color_ID))
> I see that you are doing a subselect to get [Color] inside the case, but
> this is probably not necessary. If you are only doing this to avoid
> problems where there is no usable ColorID, then perhaps.
> SELECT LTRIM(LTRIM(Te.equipmentName) + ' ' + COALESCE([Color],''))
> FROM TechEquipment Te LEFT OUTER JOIN Color C
> ON Te.ColorID = C.ID
> The sample code aliases don't make sense to me, so this above is just an
> outline. Be sure to plug in your tables and alias properly, etc.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4548E03A-B87F-43BE-ABDD-05C6766A4565@.microsoft.com...
> > Hi I am inserting some data into a temp @.table and trying to combine two
> > different fields with the + character so in the insert statement I have,
> >
> > Case when E.ColorID IS NOT Null then TE.equipmentName +
> > (Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
> >
> >
> > seems to result in an error when running the query.
> > string or binary data would be truncated!
> > If I remove the TE.equipment_Type_Name_VC it runs fine.
> > I am trying to combine the TE.equipmentName and Color
> > thanks
> > --
> > Paul G
> > Software engineer.
>
>

how to append 2 fields together

Hi I am inserting some data into a temp @.table and trying to combine two
different fields with the + character so in the insert statement I have,
Case when E.ColorID IS NOT Null then TE.equipmentName +
(Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
seems to result in an error when running the query.
string or binary data would be truncated!
If I remove the TE.equipment_Type_Name_VC it runs fine.
I am trying to combine the TE.equipmentName and Color
thanks
--
Paul G
Software engineer.Paul,
Your concatenated strings are too long for the column declaration in your
temp @.table. A couple of ways to fix it are:
1. Increase the size of the column
2. Trim the text of the concatenation. For example, assuming (perhaps
incorrectly) that equipmentName and Color are both fixed length columns and
that you would like a space between the strings, you might use something
like:
LTRIM(LTRIM(Te.equipmentName) + ' ' + (Select [Color] from Color Where
ID = E.Color_ID))
I see that you are doing a subselect to get [Color] inside the case, but
this is probably not necessary. If you are only doing this to avoid
problems where there is no usable ColorID, then perhaps.
SELECT LTRIM(LTRIM(Te.equipmentName) + ' ' + COALESCE([Color],''))
FROM TechEquipment Te LEFT OUTER JOIN Color C
ON Te.ColorID = C.ID
The sample code aliases don't make sense to me, so this above is just an
outline. Be sure to plug in your tables and alias properly, etc.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4548E03A-B87F-43BE-ABDD-05C6766A4565@.microsoft.com...
> Hi I am inserting some data into a temp @.table and trying to combine two
> different fields with the + character so in the insert statement I have,
> Case when E.ColorID IS NOT Null then TE.equipmentName +
> (Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
>
> seems to result in an error when running the query.
> string or binary data would be truncated!
> If I remove the TE.equipment_Type_Name_VC it runs fine.
> I am trying to combine the TE.equipmentName and Color
> thanks
> --
> Paul G
> Software engineer.|||Hi Russell, thanks for the response. I fixed it about 5 minutes ago, had to
increase the size on the column of the results table, one of the solutions
you specified!
Paul G
Software engineer.
"Russell Fields" wrote:

> Paul,
> Your concatenated strings are too long for the column declaration in your
> temp @.table. A couple of ways to fix it are:
> 1. Increase the size of the column
> 2. Trim the text of the concatenation. For example, assuming (perhaps
> incorrectly) that equipmentName and Color are both fixed length columns an
d
> that you would like a space between the strings, you might use something
> like:
> LTRIM(LTRIM(Te.equipmentName) + ' ' + (Select [Color] from Color W
here
> ID = E.Color_ID))
> I see that you are doing a subselect to get [Color] inside the case, b
ut
> this is probably not necessary. If you are only doing this to avoid
> problems where there is no usable ColorID, then perhaps.
> SELECT LTRIM(LTRIM(Te.equipmentName) + ' ' + COALESCE([Color],''))
> FROM TechEquipment Te LEFT OUTER JOIN Color C
> ON Te.ColorID = C.ID
> The sample code aliases don't make sense to me, so this above is just an
> outline. Be sure to plug in your tables and alias properly, etc.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4548E03A-B87F-43BE-ABDD-05C6766A4565@.microsoft.com...
>
>

How to add data in sql mobile database from sql server 2005 management studio

Hi,

After creating mobile database into sql server 2005 management studio, how to insert the records from sql server 2005 management studio into and mobile database from excel file?

Thank you

Prashant

There is no automatic tool (or out-of-box usable tool) which you can use to import data into SQL Mobile from excel or .csv file. You need to write a small app that reads the data/rwo from excel file and inserts into SQL Mobile database.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

How to add Calender control in SQL Reports

I am trying to find out on how to insert the calendar control into the
SQL Reports using SQL 2005 Reporting Services. Can someone let me know
on how to perfom this operation.
Thanks.Hi Dani, how are you?
You don't need to crate a calendar control. Just define an input
parameter as datetime and a calendar control will appear just next to
the text box to input the date.
I hope this works for you.
Dani vient de nous annoncer :
> I am trying to find out on how to insert the calendar control into the
> SQL Reports using SQL 2005 Reporting Services. Can someone let me know
> on how to perfom this operation.
> Thanks.|||On Dec 26, 12:57=A0pm, Ciro Daniele <c...@.geosolution.com.ar> wrote:
> Hi Dani, how are you?
> You don't need to crate a calendar control. Just define an input
> parameter as datetime and a calendar control will appear just next to
> the text box to input the date.
> I hope this works for you.
> Dani vient de nous annoncer :
>
> > I am trying to find out on how to insert the calendar control into the
> > SQL Reports using SQL 2005 Reporting Services. Can someone let me know
> > on how to perfom this operation.
> > Thanks.- Hide quoted text -
> - Show quoted text -
Thanks a lot, Ciro. It worked!

how to add autonumber (increment) unique ID to sqlserver

Hi,

I am a newbie learning sql server. I used to use Access table where I set the primary ID (int) as autonumber. Every insert will generate a new ID, which is incremented by 1.

How do I add an INSERT statement to add the incrementing primary ID in sql server. I noticed that you can't set the ID to autonumber as you do in Access. I read that you can set the ID as identity, which can auto increment.

How do I handle this in the INSERT cmd. The only thought is to call a sql cmd with a max on the current id and increment by 1 in vbscript. Afterwards, add the increment ID into the INSERT call.

Is there an easier way to do this?

Thanks,

JohnHi John,

Correct. You can't write to an identity field (and I didn't know you could in Access). You don't write to it, so don't include that field in your insert statement.

If you need the new ID number, return the value from the SQL Server SCOPE_IDENTITY function after you insert the row, either by returning it from your stored procedure or immediately after doing the insert. And you can batch them together so that you have only one round trip to the SQL Server server.

And there are lots of ways to do this, depending on the needs of your application.

Is that enough information? If not, just ask. It's pretty straightforward once you get to know SQL Server a bit.

Don