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 variables in a procedure in with select s

Hi,
I have code like this
:
CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
OUTPUT,@.sex VARCHAR(1) OUTPUT
AS
SELECT NAME,SEX from table1 where .....
Now I want to assign the value of Name and sex from the select statement to
variables @.name and @.sex.
How do I do that. I just do not want to execute the select statement twice
as the selection criteria is big.
Thanks,
JSSELECT @.name = name, @.sex = sex
FROM table1
WHERE (something that guarantees exactly one row)
"JS" <JS@.discussions.microsoft.com> wrote in message
news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
> Hi,
> I have code like this
> :
> CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
> OUTPUT,@.sex VARCHAR(1) OUTPUT
> AS
> SELECT NAME,SEX from table1 where .....
> Now I want to assign the value of Name and sex from the select statement
> to
> variables @.name and @.sex.
> How do I do that. I just do not want to execute the select statement twice
> as the selection criteria is big.
> Thanks,
> JS
>sql

How to assign values to variables in a procedure in with sele

I tried that but get an error for syntax check
Error141 - A select statement that assigns a value to a variable must not be
combined with data retrieval operation.
I am using SQL Server 7 .
Any help will be greatly appreciated.
Thanks,
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT @.name = name, @.sex = sex
> FROM table1
> WHERE (something that guarantees exactly one row)
>
> "JS" <JS@.discussions.microsoft.com> wrote in message
> news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
>
>> Error141 - A select statement that assigns a value to a variable must not
> be
> combined with data retrieval operation.
Then you didn't run exactly what I posted. Could you show exactly what you
tried to run?
You can't SELECT and assign in the same statement. So, if you are sure that
the WHERE clause will always limit to one row, *and* for some reason you
need to SELECT the data *and* return it in output parameters (why would you
need to do both?), you can do this:
-- assign variable values from table
SELECT @.name = name, @.sex = sex FROM table WHERE ...
-- return *variables* to client
SELECT name = @.name, sex = @.sex|||JS wrote:
> I tried that
I doubt that you tried exactly what he said. It would help if you showed us
the revised code, but I suspect your statement now looks like:
SELECT NAME,SEX,@.name = name, @.sex = sex
from table1 where .....
Right?

> but get an error for syntax check
> Error141 - A select statement that assigns a value to a variable must
> not be combined with data retrieval operation.
That's a pretty self-explanatory error message: in a single sql statement
you can either return data to the client or assign values to variables. You
cannot do both in a single statement.
I suspect what you are trying to to do is:
SELECT name = name, @.sex = sex
from table1 where .....
SELECT @.name, @.sex

> I am using SQL Server 7 .
>
Doesn't matter
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Here it is:
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Then you didn't run exactly what I posted. Could you show exactly what yo
u
> tried to run?
> You can't SELECT and assign in the same statement. So, if you are sure th
at
> the WHERE clause will always limit to one row, *and* for some reason you
> need to SELECT the data *and* return it in output parameters (why would yo
u
> need to do both?), you can do this:
> -- assign variable values from table
> SELECT @.name = name, @.sex = sex FROM table WHERE ...
> -- return *variables* to client
> SELECT name = @.name, sex = @.sex
>
>|||Here is my code
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
What I am trying to do is assign the values to the OUTPUT variables, so that
the client can see the values of the OUTPUT field.
Please help
"Bob Barrows [MVP]" wrote:

> JS wrote:
> I doubt that you tried exactly what he said. It would help if you showed u
s
> the revised code, but I suspect your statement now looks like:
> SELECT NAME,SEX,@.name = name, @.sex = sex
> from table1 where .....
> Right?
>
> That's a pretty self-explanatory error message: in a single sql statement
> you can either return data to the client or assign values to variables. Yo
u
> cannot do both in a single statement.
> I suspect what you are trying to to do is:
> SELECT name = name, @.sex = sex
> from table1 where .....
> SELECT @.name, @.sex
>
> Doesn't matter
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30)
> OUTPUT,@.sex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
(a) do you not have a primary key on SSN? If so, there is no need to set
rowcount 1, since there will only ever be a maximum of one match. If there
is no primary key, why not?
(b) sorry, but there is no way that the procedure above yields the error you
mentioned earlier. Either you transcribed it wrong or you are looking at
the wrong code.|||Also,
(a) can you come up with a more useless and generic name for your procedure
than GET_DATA? At least you use the dbo prefix...
(b) I recommend better formatting so your procedure is readable.
(c) I strongly recommend against these implicit, non-ANSI join syntaxes.
How about:
CREATE PROCEDURE dbo.GetNameSexData
@.ssn VARCHAR(9),
@.name VARCHAR(30) OUTPUT,
@.sex VARCHAR(1) OUTPUT
AS
BEGIN
SET NOCOUNT ON
SET ROWCOUNT 1
SELECT
@.name = nt.Name,
@.sex = ot.Sex
FROM
Name_Table nt
INNER JOIN Other_Table ot
ON nt.ssn = ot.ssn
WHERE nt.ssn = @.ssn
END
GO
Now, if that produces an error when you *call* it, show the method you used
to *call* it.|||Got it I was declaring the variables but assigining all the values in the
select statement for the output type. Thanks very much for all the help
"JS" wrote:
> Here is my code
> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.s
ex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
> What I am trying to do is assign the values to the OUTPUT variables, so th
at
> the client can see the values of the OUTPUT field.
> Please help
> "Bob Barrows [MVP]" wrote:
>

How to append a header row in SELECT

Hi All,

When calling my SELECT statement, I would like to have Column name and Data type as the first record. Is this possible to do?
I would like to make a SP that would select all data from any table(with a header row) with just:

_sp_getData ' table_name'

I looked at sysobjects, and syscolumns tables, but was unable to put it all together. I have over 200+ tables, so creating variables for every column is very cumbersome.

Something like this:

select 0 AS SortCol ,
 cast(min(case ordinal_position when 1 then column_name end) as varchar) as col1,
cast(min(case ordinal_position when 2 then column_name end) as varchar) as col2,
cast(min(case ordinal_position when 3 then column_name end) as varchar) as col3,
cast(min(case ordinal_position when 4 then column_name end) as varchar) as col4,
cast(min(case ordinal_position when 5 then column_name end) as varchar) as col5,
cast(min(case ordinal_position when 6 then column_name end) as varchar) as col6,
cast(min(case ordinal_position when 7 then column_name end) as varchar) as col7,
cast(min(case ordinal_position when 8 then column_name end) as varchar) as col8,
cast(min(case ordinal_position when 9 then column_name end) as varchar) as col9
 from information_schema.columns where table_name = 'authors'
union all
select 1 , au_id, au_lname, au_fname, phone, address, city, state, zip,
cast(contract as varchar)
 from authors

Is not an option.

Thank you very much in advance,
Vla OrlovskyJust an advice: do not do it. THis is not SQL. You add an additional non-data row int data. VERY bad style.

Especially as you then go on and say the one way to do it (union) is not an option.|||Hi Tom,

Thanks for reply.

In my case, I just want to export data into a VB RecordSet and into a CSV text file.
My co-workers want to have the data to be imported into other places.
I just want to create a simple GUI that allows them to get the data without involving me.

Sincerely,
Vlad Orlovsky|||So what?

Before exposrint to a CSV file, write the first line with the names of the columns.

Interesting enough, the information is in the VB recordset.|||If I understand correctly, before you output the actual data to CSV, you can just loop through the fields collection on the first row of your recordset and output the Name and Type property like below:

For intCounter = 0 To oRS.Fields.Count - 1
strName = oRS.Fields(intCounter).Name
strType = oRS.Fields(intCounter).Type
Next|||Hi,

I wanted to get the data the most fastest way posible.(with very little IIS processing)

Sincerely,
Vlad Orlovsky

How to add two integer columns in a select query?

I must be missing something, but how do you do this?
select myintcol1+myintcol2 as sumcol from...
Thanks!
Nevermind, it does work like that, I had a typo!!

How to add two integer columns in a select query?

I must be missing something, but how do you do this?
select myintcol1+myintcol2 as sumcol from...
Thanks!Nevermind, it does work like that, I had a typo!!sql

How to add two integer columns in a select query?

I must be missing something, but how do you do this?
select myintcol1+myintcol2 as sumcol from...
Thanks!Nevermind, it does work like that, I had a typo!!

How to add languages to the table sys.fulltext_languages?

HELLO ALL!

select * from sys.fulltext_languages

this query results nothing, so I need to add languages to sqlserver to be used with FTS.

Is there any way to do that?

THANK YOU!!!

It is best to not post multiple versions of your question like this. Post and wait for a response. That shows respect for the folks here since they don't have to spend time answering a question that was answered in another posting.

|||

See the follwing links for clarification:

default full-text language Option
"The default value of this option is the language of the server."
http://msdn2.microsoft.com/en-us/library/ms180854.aspx

Setting Server Configuration Options
http://msdn2.microsoft.com/en-us/library/ms189631.aspx

sys.fulltext_languages (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176076.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

how to add extra columns to a report that are not in select query or stored proc

Hi,

I am new to reporting.I have 10 columns coming from the sql query.I need to add 2 more columns to the same report.These 2 columns come from the values the user selected in the dropdownlist on a webpage(From where we are calling the Report).How to add these columns to the report from the webpage.Please guide me.

in the textbox use

=Parameters!parameterName.Value

|||

you create input parameters and supply the value when the rpt is launched from the web, then in the text box u place the i/p paramter inst of keeping the table field

How to add document type in full - text search (ifiter)

Dear all,
I use full-text search in SQL2005, but i discover it only support some
document types (.doc \ .xls .. , select * from sys.fulltext_document_types
),i want to it also support PDF type, well then, how i can do it?
by the way. I download a ifilter from adobe website and installed. but
also can't support the pdf type.
thanks & regards
jp ye
"jp" <yjp_it@.yahoo.com.cn> wrote in message
news:3E9576E9-041E-496E-91C4-4AD1B39360CB@.microsoft.com...
> Dear all,
> I use full-text search in SQL2005, but i discover it only support some
> document types (.doc \ .xls .. , select * from sys.fulltext_document_types
> ),i want to it also support PDF type, well then, how i can do it?
> by the way. I download a ifilter from adobe website and installed. but
> also can't support the pdf type.
You might get a faster answer if you re-post this to
microsoft.public.sqlserver.fulltext. They deal specifically with FTS
issues.

How to add document type in full - text search (ifiter)

Dear all,
I use full-text search in SQL2005, but i discover it only support some
document types (.doc \ .xls .. , select * from sys.fulltext_document_types
),i want to it also support PDF type, well then, how i can do it?
by the way. I download a ifilter from adobe website and installed. but
also can't support the pdf type.
thanks & regards
jp ye"jp" <yjp_it@.yahoo.com.cn> wrote in message
news:3E9576E9-041E-496E-91C4-4AD1B39360CB@.microsoft.com...
> Dear all,
> I use full-text search in SQL2005, but i discover it only support some
> document types (.doc \ .xls .. , select * from sys.fulltext_document_types
> ),i want to it also support PDF type, well then, how i can do it?
> by the way. I download a ifilter from adobe website and installed. but
> also can't support the pdf type.
You might get a faster answer if you re-post this to
microsoft.public.sqlserver.fulltext. They deal specifically with FTS
issues.

How to add document type in full - text search (ifiter)

Dear all,
I use full-text search in SQL2005, but i discover it only support some
document types (.doc \ .xls .. , select * from sys.fulltext_document_types
),i want to it also support PDF type, well then, how i can do it?
by the way. I download a ifilter from adobe website and installed. but
also can't support the pdf type.
--
thanks & regards
jp ye"jp" <yjp_it@.yahoo.com.cn> wrote in message
news:3E9576E9-041E-496E-91C4-4AD1B39360CB@.microsoft.com...
> Dear all,
> I use full-text search in SQL2005, but i discover it only support some
> document types (.doc \ .xls .. , select * from sys.fulltext_document_types
> ),i want to it also support PDF type, well then, how i can do it?
> by the way. I download a ifilter from adobe website and installed. but
> also can't support the pdf type.
You might get a faster answer if you re-post this to
microsoft.public.sqlserver.fulltext. They deal specifically with FTS
issues.

how to add aspnet user to sqlexpress

Hi guys

I am trying to add aspnet user to sqlexpress. I am able to select the asp.net however when I click on OK button,

it is asking me to enter a name for it. COuld you tell me what name I should enter? I tried computername\aspnet didn't help?

is there better way to do this?

thanks for your help

Cemal

Hi patron,

What are the steps you did for creating user? We can simply create user by following steps:

In Management Studio, locate to Server name | Security | Logins, right click Logins node and select "New Login…", click Search button in General panel. Then the "Select User or Group" dialog appears. Please be sure the location is your computer name. Input ASPNET or Network Service in the TextBox and then click "Check Names".

how to add a multi value select prompt

Thanks Wayne for the useful information but i am experiencing another
problem
I am not able to add a prompt in the reportwhich can allow multiple
values,
as in, if i want to write a query
select * from departments where departmentid in (@.PromptName)
i want to see the data for more than 1 departments, but if i specify a
comma separated list in the prompt, it gives me an error.......
please tell me if there is another way of doing so.Check
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/22/200.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Gunjan jain" <gunjan@.nagarro.com> wrote in message
news:f8d018ec.0501172039.59a24cd2@.posting.google.com...
> Thanks Wayne for the useful information but i am experiencing another
> problem
> I am not able to add a prompt in the reportwhich can allow multiple
> values,
> as in, if i want to write a query
> select * from departments where departmentid in (@.PromptName)
> i want to see the data for more than 1 departments, but if i specify a
> comma separated list in the prompt, it gives me an error.......
> please tell me if there is another way of doing so.

How to add a date range of drop down list for a SQL query?

Hi All,
I'm trying to create a report that let the user select the date range (such
as previous month, YTD, Oct, Nov,Dec, etc.). I know that I need to create a
SQL query like "select * from MyTable where selectdate between @.FromDate and
@.ToDate", but there are 2 parameters, but the date range drop down list just
have one parameter (For example, @.SelectDate), how could I pass the
@.SelectDate value to the @.FromDate and @.Todate?
I tried to search the MSDN but could not get the answer? Anyone could help?
Sample are appreciated if has.
Thanks a lot.
BillUse 2 comboboxes, one for the @.From another for the @.To date each with their
proper dataset:
- For @.From use something like "select mydates from dimTime ... "
- For @.To use something like "select mydates from dimTime where dates>=@.From
..."
(pseudo-code, you get the idea)
hth,
Tom
"Bill" wrote:
> Hi All,
> I'm trying to create a report that let the user select the date range (such
> as previous month, YTD, Oct, Nov,Dec, etc.). I know that I need to create a
> SQL query like "select * from MyTable where selectdate between @.FromDate and
> @.ToDate", but there are 2 parameters, but the date range drop down list just
> have one parameter (For example, @.SelectDate), how could I pass the
> @.SelectDate value to the @.FromDate and @.Todate?
> I tried to search the MSDN but could not get the answer? Anyone could help?
> Sample are appreciated if has.
> Thanks a lot.
> Bill
>

how to access the info of a table created in other database

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tab_db1'

The above query will give the information of the table 'tab_db1' if it is available in the current database (say db1) connected.

How can i access the information of a table (say 'tab_db2') which is created under a different database say db2 from the current db connected say db1.

I tried the above query with changing the table_name to 'db2..tab_db2'
but went invain.

sysobjects also dint work..

Any help on this will be appreciated..

regards,
Sathya Vselect * from db2.information_schema.tables|||Thanks Tom, for answering my query!