Auditing changes to Scheduled Jobs

Hello,

Is there a way that I can create an audit trail of changes that are made to scheduled jobs in SQL Server 2000?

Thanks

This question is not directly related to SSIS, so I would try posting in the SQL Server Tools General forum to get more responses.

Bob

audit trail...

Hi...
A much lamented question, I guess..

I'm trying to create a simple audit trail.
log the changes to an SQL 2000 table, so that they are written into a
mirror table. The entire record, only the updated one, i.e. if say
only one field changes, the audit table will be inserted with one
record that has one field changed. if the record has been deleted, it
still will be written.
I'm not worrying about additional fields to the audit table containing
descriptive flags of what action took place yet. I just want the
mirror image for starters.

I got the script of the 'create table' off Query analyzer. created the
audit table.
the trigger looks like this:

CREATE TRIGGER dt_tbl1_audit
on tbl1
for insert, update, delete

AS
insert into tbl1_audit
select * from inserted

the table has about 50 fields or so, so I tried to make do with *'s.
didn't work, so I tried copying and pasting the explicit list of field
names
instead (though I'm not sure why it needs that if the two tables are
identically structured).

in either case, if I update any field on the audited table, I get this
error:
(after getting the warning that the results may take a long time to
process etc, the original table has over 100,000 rows)

"another user has modified the contents of this table or view,
the database row you are modifying no longer exists in the database
database error: insert error:
column name or number of supplied values does not match table
definition"

I'm not sure what's wrong, the two tables are identical (I copy pasted
the create table script with no changes). no other users except me on
this database.
i've removed all constraints and indexes from the audit table.

thanksHi

It seems that you are having problems with posting!!!

If speed is important you should think about keeping simple copies of the
inserted and deleted tables (possibly with other columns such as a timestamp
and user name) as this will mean that the trigger the least processing and
not causing you transactions to be open for a elongated period. The work of
resolving what columns have been updated can then be done either when
reporting or at a more convenient time.

If you wish to do this processing withing the trigger check out Books online
under the "Create Trigger" topic, you can find information about using the
UPDATED() and COLUMNS_UPDATED() functions and example of how to use them.
Another alternative method is to use a log file reading product such as
those from Lumigent (Lumigent Log Explorer) http://www.lumigent.com/ or PI,
http://www.logpi.com
and process the information in the log files.

HTH

John

"Me" <heruti@.lycos.com> wrote in message
news:2d4c3262.0411261512.6d8072aa@.posting.google.c om...
> Hi...
> A much lamented question, I guess..
> I'm trying to create a simple audit trail.
> log the changes to an SQL 2000 table, so that they are written into a
> mirror table. The entire record, only the updated one, i.e. if say
> only one field changes, the audit table will be inserted with one
> record that has one field changed. if the record has been deleted, it
> still will be written.
> I'm not worrying about additional fields to the audit table containing
> descriptive flags of what action took place yet. I just want the
> mirror image for starters.
> I got the script of the 'create table' off Query analyzer. created the
> audit table.
> the trigger looks like this:
> CREATE TRIGGER dt_tbl1_audit
> on tbl1
> for insert, update, delete
> AS
> insert into tbl1_audit
> select * from inserted
>
> the table has about 50 fields or so, so I tried to make do with *'s.
> didn't work, so I tried copying and pasting the explicit list of field
> names
> instead (though I'm not sure why it needs that if the two tables are
> identically structured).
> in either case, if I update any field on the audited table, I get this
> error:
> (after getting the warning that the results may take a long time to
> process etc, the original table has over 100,000 rows)
> "another user has modified the contents of this table or view,
> the database row you are modifying no longer exists in the database
> database error: insert error:
> column name or number of supplied values does not match table
> definition"
> I'm not sure what's wrong, the two tables are identical (I copy pasted
> the create table script with no changes). no other users except me on
> this database.
> i've removed all constraints and indexes from the audit table.
>
> thanks|||Me (heruti@.lycos.com) writes:
> I got the script of the 'create table' off Query analyzer. created the
> audit table.
> the trigger looks like this:
> CREATE TRIGGER dt_tbl1_audit
> on tbl1
> for insert, update, delete
> AS
> insert into tbl1_audit
> select * from inserted
>
> the table has about 50 fields or so, so I tried to make do with *'s.
> didn't work, so I tried copying and pasting the explicit list of field
> names
> instead (though I'm not sure why it needs that if the two tables are
> identically structured).

Depends on what columns there are in the tables. If you have an IDENTITY
colunm in the source table, the corresponding table in the audit table
cannot have the IDENTITY property. And if there are timestamp columns,
you would have to make them binary(8) in the target table.

In any case, some sort of a primary key for the target table would be a good
idea.

> in either case, if I update any field on the audited table, I get this
> error:
> (after getting the warning that the results may take a long time to
> process etc, the original table has over 100,000 rows)

So where does this warning come from?

> "another user has modified the contents of this table or view,
> the database row you are modifying no longer exists in the database
> database error: insert error:
> column name or number of supplied values does not match table
> definition"
> I'm not sure what's wrong, the two tables are identical (I copy pasted
> the create table script with no changes). no other users except me on
> this database.
> i've removed all constraints and indexes from the audit table.

Well, we don't even know the definition of the tables, so how could we
tell what is going on?

Do you get this error when you perform an update from Query Analyzer? If
so, can you cut and paste the complete error message? The error message
should include procedure name and line number where the message appears.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes, I did have trouble posting, I do apologize..

IE6 reported some sort of 404 error when I clicked submit in the
dejanews post. I assumed it didn't & tried several dozen times until I
accidentally discovered that it did post (dejanews says it takes
several hours to post, so traditionally I wouldn't have discovered
this, but this time I stumbled on another site, sort of a gateway to
google groups, which showed my post immediately... seems useful.
http://news-reader.org/comp.databases.ms-sqlserver/

re the audit, I did get the code to work both ways, with *'s notation
and also with detail listing of all the fields. so this post is
generally for the benefit of other befuddled customers on my trail..

The following trigger works. audits updates/inserts on the table
tblSource, into the log table tblSource_Audit which has the same
structure plus the two fields 'LogActionType' (varchar 10) and
'LogDate':

CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS
INSERT INTO tblSource_audit
select 'insert/upd',GetDate(),
* from Inserted ins
GO

Still, I thought it would be safer (future maintenance wise, so the
trigger won't break if fields are added to the source table)
to convert the whole thing into detailed column notation (too long to
list here), and Later add a little condition code to it that would
post 'insert' and 'update' strings identifying the two operations and
not the combined string above:

CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS

Declare @.ActionType VARCHAR(10)
Declare @.DeleteCnt int
set @.DeleteCnt = (select count(*) from deleted)
if @.DeleteCnt = 0
begin
set @.ActionType = 'Insert'
end
ELSE
set @.ActionType = 'Update'

INSERT INTO tblSource_audit
select @.ActionType,GetDate(),
* from Inserted ins
GO

Thanks everyone for your help. Any more comments, of course, welcome.

audit trail triggers

Hello.

I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,FieldName,OldValue,NewValue,UpdateDate,t ype,UserName.
Triggers on each table were set to do the job and everything was fine except
that in the audit trail you couldn't know which row exacltly was
updated/inserted/deleted...Therefore I introduced 3 additional columnes
(RowMark1, RowMark2, RowMark3) which should identify the
inserted/updated/deleted row.
For example, RowMark1 could be foreign key, RowMark2 could be primary key,
and RowMark3 could be autonumber ID.
But, when I have several rows updated, RowMark columnes values are identical
in all rows in the audit trail table! What is wrong with my code, and how to
solve it ?

Thank you in advance!

CREATE TRIGGER Trigger_audit_TableName
ON dbo.TableName
FOR DELETE, INSERT, UPDATE
AS BEGIN
declare @.type nvarchar(20) ,
@.UpdateDate datetime ,
@.UserName nvarchar(100),
@.RowMark1 nvarchar (100),
@.RowMark2 nvarchar (100),
@.RowMark3 nvarchar (100)

if exists (select * from inserted) and exists (select * from
deleted)
select @.type = 'UPDATE',
@.RowMark1=d.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=d.ID
from deleted d
else if exists (select * from inserted)
select @.type = 'INSERT',
@.RowMark1=i.ForeignKeyField,@.RowMark2=i.PrimaryKey Field,@.RowMark3=i.ID
from inserted i
else
select @.type = 'DELETE',
@.RowMark1=d.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=d.ID
from deleted d

select @.UpdateDate = getdate() ,
@.UserName = USER

/*The following code is repeated for every field in a table*/
if update (FieldName) or @.type = 'DELETE'
insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type,RowMark1,RowMark2,RowMark3)
select 'Descriptive Table Name', convert(nvarchar(100), 'Descriptive
Field Name'),
convert(nvarchar(1000),d.FieldName),
convert(nvarchar(1000),i.FieldName),
@.UpdateDate, @.UserName, @.type, @.RowMark1, @.RowMark2,
@.RowMark3
from inserted i
full outer join deleted d
on i.ID = d.ID
where (i.FieldName <> d.FieldName
or (i.FieldName is null and d.FieldName is not null)
or (i.FieldName is not null and d.FieldName is null))

ENDOn Thu, 31 Mar 2005 20:10:46 +0200, Zlatko Mati wrote:

>Hello.
>I tried to implement audit trail, by making an audit trail table with the
>following fileds:
>TableName,FieldName,OldValue,NewValue,UpdateDate,t ype,UserName.
>Triggers on each table were set to do the job and everything was fine except
>that in the audit trail you couldn't know which row exacltly was
>updated/inserted/deleted...Therefore I introduced 3 additional columnes
>(RowMark1, RowMark2, RowMark3) which should identify the
>inserted/updated/deleted row.
>For example, RowMark1 could be foreign key, RowMark2 could be primary key,
>and RowMark3 could be autonumber ID.
>But, when I have several rows updated, RowMark columnes values are identical
>in all rows in the audit trail table! What is wrong with my code, and how to
>solve it ?
>Thank you in advance!

Hi Zlatko,

A trigger is fired once per statement execution, not once per row
affected. The changes you made, like for example this one:

> select @.type = 'UPDATE',
> @.RowMark1=d.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=d.ID
>from deleted d

will set the variables to the values for one of the rows that were
affected by the update. This variable is then used in all inserts to the
audit table!

You should forget the @.RowMark1, -2, and -3 variables. Instead, change
the code to insert audit data to something like this:

insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type,RowMark1,RowMark2,RowMark3)
select 'Descriptive Table Name', convert(nvarchar(100), 'Descriptive
Field Name'),
convert(nvarchar(1000),d.FieldName),
convert(nvarchar(1000),i.FieldName),
@.UpdateDate, @.UserName, @.type,
COALESCE (d.ForeignKeyField, i.ForeignKeyField),
COALESCE (d.PrimaryKeyField, i.PrimaryKeyField),
COALESCE (d.ID, i.ID)
from inserted i
full outer join deleted d
on i.ID = d.ID
where (i.FieldName <> d.FieldName
or (i.FieldName is null and d.FieldName is not null)
or (i.FieldName is not null and d.FieldName is null))

I'd like to add that I'm not very fond of your audit table design. I
personally prefer to use several audit tables: one for each table that
needs auditing, with the same columns, plus extra columns such as
DatetimeOfChange (added as extra column to the primary key) and userid.
This table will receive a complete copy of a row's data whenever it is
changed. But hey - if this design works for you, then by all means use
it.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Zlatko Mati (zlatko.matic1@.sb.t-com.hr) writes:
> But, when I have several rows updated, RowMark columnes values are
> identical in all rows in the audit trail table!

Of course:

>@.RowMark1=d.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=d.ID
> from deleted d

If deleted contains 15 rows, how would @.RowMark1 get more than one value?

Yes, triggers fires once per statement.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks.

"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> je napisao u poruci interesnoj
grupi:9hpo41hcd4b2gifouvm3o13pj48r3ereqb@.4ax.com.. .
> On Thu, 31 Mar 2005 20:10:46 +0200, Zlatko Mati wrote:
>>Hello.
>>
>>I tried to implement audit trail, by making an audit trail table with the
>>following fileds:
>>TableName,FieldName,OldValue,NewValue,UpdateDate,t ype,UserName.
>>Triggers on each table were set to do the job and everything was fine
>>except
>>that in the audit trail you couldn't know which row exacltly was
>>updated/inserted/deleted...Therefore I introduced 3 additional columnes
>>(RowMark1, RowMark2, RowMark3) which should identify the
>>inserted/updated/deleted row.
>>For example, RowMark1 could be foreign key, RowMark2 could be primary key,
>>and RowMark3 could be autonumber ID.
>>But, when I have several rows updated, RowMark columnes values are
>>identical
>>in all rows in the audit trail table! What is wrong with my code, and how
>>to
>>solve it ?
>>
>>Thank you in advance!
> Hi Zlatko,
> A trigger is fired once per statement execution, not once per row
> affected. The changes you made, like for example this one:
>> select @.type = 'UPDATE',
>>
>> @.RowMark1=d.ForeignKeyField,@.RowMark2=d.PrimaryKey Field,@.RowMark3=d.ID
>>from deleted d
> will set the variables to the values for one of the rows that were
> affected by the update. This variable is then used in all inserts to the
> audit table!
> You should forget the @.RowMark1, -2, and -3 variables. Instead, change
> the code to insert audit data to something like this:
> insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
> UpdateDate, UserName, type,RowMark1,RowMark2,RowMark3)
> select 'Descriptive Table Name', convert(nvarchar(100), 'Descriptive
> Field Name'),
> convert(nvarchar(1000),d.FieldName),
> convert(nvarchar(1000),i.FieldName),
> @.UpdateDate, @.UserName, @.type,
> COALESCE (d.ForeignKeyField, i.ForeignKeyField),
> COALESCE (d.PrimaryKeyField, i.PrimaryKeyField),
> COALESCE (d.ID, i.ID)
> from inserted i
> full outer join deleted d
> on i.ID = d.ID
> where (i.FieldName <> d.FieldName
> or (i.FieldName is null and d.FieldName is not null)
> or (i.FieldName is not null and d.FieldName is null))
> I'd like to add that I'm not very fond of your audit table design. I
> personally prefer to use several audit tables: one for each table that
> needs auditing, with the same columns, plus extra columns such as
> DatetimeOfChange (added as extra column to the primary key) and userid.
> This table will receive a complete copy of a row's data whenever it is
> changed. But hey - if this design works for you, then by all means use
> it.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Audit Trail Processing?

Audit Trail Processing?

Does anyone know of a good article or book which describes how to implement Audit Trail processing for an application which runs on a SQL Server Database?

Please let me know the link and etc.

R

Here are few links:

http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec04.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx

http://www.microsoft.com/sql/partners/technologysolutions/security.asp

Additionally, auditing also depends on your requirements and how you want to implement the process. You can also look at the 3rd party solutions on top of SQL Server.

Audit Trail for Structural Changes

audit trail for structural changes in SQL2000There is no way to do this as there are no DDL triggers in SQL Server 2000.

Jens K. Suessmeyer.

http://www.sqlserver2008.de

Audit trail - Sql Server 2000

Hi,
Greetings to all.
We are building up a new application using SQL Server 2000 & .NET.
Need to build basic “Audit trail” process, in which need to store {User_
Id/
DateTime/ Status/Process Name}.
What’s the best strategy for implementing “Audit trail” in SQL Server
2000
Applications?
Thanks for your time.I'm assuming that "Status" and "Process Name" are not columns in tables, but
rather logical names in your application. The simplest way is to use
something like .NET Enterprise Library or even build your own simple logic
in your application to keep trace of this.
Typically, in the database, if you want to track changes made to your
tables, you use triggers, but in your case, I think you want to track what
"functions" a user is using?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
> Hi,
> Greetings to all.
> We are building up a new application using SQL Server 2000 & .NET.
> Need to build basic "Audit trail" process, in which need to store
> {User_Id/
> DateTime/ Status/Process Name}.
> What's the best strategy for implementing "Audit trail" in SQL Server 2000
> Applications?
> Thanks for your time.
>|||Thanks for your response. Let me explain my situation once again:-
e.g. I have a screen & different status of order on that screen i.e.
Created/Billed/Processed/Manufactured/Shipped.
What I want to do is, I want to record, which user had changed above order
status at what time?
Which means for each record, I want to build an Audit Trail?
I am aware that ,I can create my own Audit table & can create DB Triggers
for recording details ,or stored Proc can do same for me…
I am looking for what’s the best strategy to build an Audit trail.
Thank you very much for your precious time & Valuable inputs.
Regards.
"SriSamp" wrote:

> I'm assuming that "Status" and "Process Name" are not columns in tables, b
ut
> rather logical names in your application. The simplest way is to use
> something like .NET Enterprise Library or even build your own simple logic
> in your application to keep trace of this.
> Typically, in the database, if you want to track changes made to your
> tables, you use triggers, but in your case, I think you want to track what
> "functions" a user is using?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
>
>|||I would suggest triggers and audit tables. Try to keep the trigger as light
as possible. What kind of load (# of users, volume of data, etc.) does your
system have?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
> Thanks for your response. Let me explain my situation once again:-
> e.g. I have a screen & different status of order on that screen i.e.
> Created/Billed/Processed/Manufactured/Shipped.
> What I want to do is, I want to record, which user had changed above order
> status at what time?
> Which means for each record, I want to build an Audit Trail?
> I am aware that ,I can create my own Audit table & can create DB Triggers
> for recording details ,or stored Proc can do same for me.
> I am looking for what's the best strategy to build an Audit trail.
> Thank you very much for your precious time & Valuable inputs.
> Regards.
>
> "SriSamp" wrote:
>|||Hi,
Apart from Trigger & Audit table ,does sql server comes with default
keywords like AUdit ? Or any other Standard/better approach to hadle it ?
Oracle allows us to audit data using the ‘AUDIT’ command. For example,
‘AUDIT DELETE ON my_table;’
Regards
"Alain Quesnel" wrote:

> I would suggest triggers and audit tables. Try to keep the trigger as ligh
t
> as possible. What kind of load (# of users, volume of data, etc.) does you
r
> system have?
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
>
>|||Hi,
In terms of Users/Load ,it's 50 -75 users using it at one point.
"Alain Quesnel" wrote:

> I would suggest triggers and audit tables. Try to keep the trigger as ligh
t
> as possible. What kind of load (# of users, volume of data, etc.) does you
r
> system have?
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "SSUK" <SSUK@.newsgroups.nospam> wrote in message
> news:ADC87BEF-D824-49DD-B840-304CD99C332D@.microsoft.com...
>
>|||I don't think there are any default tools, but I'd personally steer
clear of triggers. While they're great at doing what they do, and lend
themselves naturally to an audit trail, I've found the main drawback of
them is that you can't see them. When someone else comes to maintain
your application, they would have to know that the triggers are there.
I know that with good documentation and skilled staff this would appear
simple, but you can't generally count on either.
My prefered method (given this drawback of triggers) is to write a
sufficiently generic auditing stored procedure, then within your insert
/ update / delete stored procs call this method. This has the advantage
that your auditing behaviour is centralised, and therefore if you need
to change it you won't need to iterate through the triggers, but also
that the operation is visible to anyone else who needs to pick up your
app.
I suspect this is a sufficiently controversial suggestion to get
utterly thrashed on here now, so... off you go guys, above the belt
with the punches please.|||Thanks for your reply.I agree to your suggestion to quite some extent.But i
am wondering , do we have an better way / inbuilt way of handling this ? Or
any more standard way (Best practise) of handling this ?
"Will" wrote:

> I don't think there are any default tools, but I'd personally steer
> clear of triggers. While they're great at doing what they do, and lend
> themselves naturally to an audit trail, I've found the main drawback of
> them is that you can't see them. When someone else comes to maintain
> your application, they would have to know that the triggers are there.
> I know that with good documentation and skilled staff this would appear
> simple, but you can't generally count on either.
> My prefered method (given this drawback of triggers) is to write a
> sufficiently generic auditing stored procedure, then within your insert
> / update / delete stored procs call this method. This has the advantage
> that your auditing behaviour is centralised, and therefore if you need
> to change it you won't need to iterate through the triggers, but also
> that the operation is visible to anyone else who needs to pick up your
> app.
> I suspect this is a sufficiently controversial suggestion to get
> utterly thrashed on here now, so... off you go guys, above the belt
> with the punches please.
>|||I don't know of any in-built methods, I'm pretty sure there aren't any.
However the information you'll be recording is usually fairly specific
to your app. My advice would be either to maintain auditing in the
stored procs, or, depending on the structure of your web app you could
enhance your data access layer to add a custom audit module that
controls all the auditing.
Personally I prefer keeping out of SQL server based auditing for
several reasons:
1) you can more easily re-use your auditing code
2) you can more easily audit to a centralised database for several apps
3) by auditing to a separate database you are not having to back up
tracing data along with your actual app data (though you do have to
consider re-synchronisation when restoring)
4) you are able to capture web app info more easily (e.g. if you're
using forms based authentication you can capture usernames, or you can
capture the page that the update originated from).
5) the auditing is then done for your application, rather than on your
db. This has the advantage that if someone else makes a custom update
to your database, you can't accuse the application of it. On the other
hand, you don't have the traceability of what was done (but personally
I prefer the idea that your app only audits things that it does).
6) the auditing is more visible and more maintainable
I could probably come up with more reasons, but this should indicate my
preference.
Perhaps someone else could recommend some off the shelf plug-ins for
this?
Will|||Just to give you a couple of more options to evaluate:
Have a server-side profiler trace going in which you capture the relevant ev
ents.
Use some of the 3:rd party (transaction) log reader tools, where some has ex
plicit audit
capabilities. The transaction log contains information about all modificatio
ns and some of these
tools can also log SELECT using a profiler trace in conjunction with the tra
nsaction log. I've
listed some of these log reader tools on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SSUK" <SSUK@.newsgroups.nospam> wrote in message
news:7C422ADD-588A-4B34-BD0B-727C6F625BF9@.microsoft.com...
> Hi,
> Greetings to all.
> We are building up a new application using SQL Server 2000 & .NET.
> Need to build basic "Audit trail" process, in which need to store {User_Id
/
> DateTime/ Status/Process Name}.
> What's the best strategy for implementing "Audit trail" in SQL Server 2000
> Applications?
> Thanks for your time.
>

Audit Trail - getting the current username.

Hi, I have a couple of questions on writing an audit trail/last updated
feature for my database.
First, How can I programatically get the current user in SQL when
executing a stored procedure.I have tried using sysusers table and
CURRENT_USER but only get the value dbo. I am using integrated
security. sp_who seesm to have the data but how can i use it?
Second, is it actually prefered approach to pass this value in from IIS
or the calling application as a parameter? I'd prefer it be hardcoded
in the stoerd procedure. Are there any divantages to this ?
Third question, I am doing this for a simple audit of LastUpdated and
LastUpDatedBy with additional columns on tables (see below), I know a
trigger and history table is much more comprehensive, but are there any
other approaches to consider or built in tools for tracking changes to
records and capturing who/when data.
thanks
hals_left
CREATE PROCEDURE [add_record]
@.RecordID smallInt,
AS
Begin
INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
VALUES ( @.RecordID , GetDate(), CURRENT_USER )
End
GOSELECT SYSTEM_USER
That will return the SQL login name or the windows domain and username.
HTH
--
Gail Shaw (MCSD)
http://gail.rucus.net/
"cc900630@.ntu.ac.uk" wrote:

> Hi, I have a couple of questions on writing an audit trail/last updated
> feature for my database.
> First, How can I programatically get the current user in SQL when
> executing a stored procedure.I have tried using sysusers table and
> CURRENT_USER but only get the value dbo. I am using integrated
> security. sp_who seesm to have the data but how can i use it?
> Second, is it actually prefered approach to pass this value in from IIS
> or the calling application as a parameter? I'd prefer it be hardcoded
> in the stoerd procedure. Are there any divantages to this ?
> Third question, I am doing this for a simple audit of LastUpdated and
> LastUpDatedBy with additional columns on tables (see below), I know a
> trigger and history table is much more comprehensive, but are there any
> other approaches to consider or built in tools for tracking changes to
> records and capturing who/when data.
> thanks
> hals_left
> CREATE PROCEDURE [add_record]
> @.RecordID smallInt,
> AS
> Begin
> INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
> VALUES ( @.RecordID , GetDate(), CURRENT_USER )
> End
> GO
>|||
"cc900630@.ntu.ac.uk" schrieb:

> Hi, I have a couple of questions on writing an audit trail/last updated
> feature for my database.
> First, How can I programatically get the current user in SQL when
> executing a stored procedure.I have tried using sysusers table and
> CURRENT_USER but only get the value dbo. I am using integrated
> security. sp_who seesm to have the data but how can i use it?
> Second, is it actually prefered approach to pass this value in from IIS
> or the calling application as a parameter? I'd prefer it be hardcoded
> in the stoerd procedure. Are there any divantages to this ?
> Third question, I am doing this for a simple audit of LastUpdated and
> LastUpDatedBy with additional columns on tables (see below), I know a
> trigger and history table is much more comprehensive, but are there any
> other approaches to consider or built in tools for tracking changes to
> records and capturing who/when data.
> thanks
> hals_left
> CREATE PROCEDURE [add_record]
> @.RecordID smallInt,
> AS
> Begin
> INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
> VALUES ( @.RecordID , GetDate(), CURRENT_USER )
> End
> GO
Try SUser_SName() ...|||Hi,
For the first question:
You can capture the current user in SQL using SYSTEM_USER.
For the second question:
When you can capture this in SQL through SYSTEM_USER keyword, i hope you can
avoid hardcoding.
For the third Question:
You can launch the enterprise manager. Right click on the database to look
for properties. In the properties window, go to the security tab and set the
Audit level to your choice.After you change audit settings, you need to
restart the server. But this writes to Application Log might degrade
performance. I think the approach you are using should be fair enough.
I hope this will be of some help to you.
"cc900630@.ntu.ac.uk" wrote:

> Hi, I have a couple of questions on writing an audit trail/last updated
> feature for my database.
> First, How can I programatically get the current user in SQL when
> executing a stored procedure.I have tried using sysusers table and
> CURRENT_USER but only get the value dbo. I am using integrated
> security. sp_who seesm to have the data but how can i use it?
> Second, is it actually prefered approach to pass this value in from IIS
> or the calling application as a parameter? I'd prefer it be hardcoded
> in the stoerd procedure. Are there any divantages to this ?
> Third question, I am doing this for a simple audit of LastUpdated and
> LastUpDatedBy with additional columns on tables (see below), I know a
> trigger and history table is much more comprehensive, but are there any
> other approaches to consider or built in tools for tracking changes to
> records and capturing who/when data.
> thanks
> hals_left
> CREATE PROCEDURE [add_record]
> @.RecordID smallInt,
> AS
> Begin
> INSERT INTO myTable(RecordID,LastUpdated,LastUpdated
By )
> VALUES ( @.RecordID , GetDate(), CURRENT_USER )
> End
> GO
>