how to enable auditing in SQL (or how to maintain session state variables)

My application is developed in VB.NET and uses a single/same connection
string for all users (all
installations), which uses SQL Server 2000 as the data store.
But I need to keep track of who inserted a record and who modified a record
(along with the dates and times).
Using triggers, I can easily setup the dates when a record was added and
last modified.
But how can i determine what users is connected to the application and is
making the changes.
As this information is stored in the application.
Is there a way that i can inform SQL what user is connecting to the server
(database), or set this information in SQL Server such that this information
is stored for each session. I.e. is there some technology similar to session
states in websites in Sql server. where i can store session variables for
each session on the website, thus enabling me to store session variables in
sql server for each session (connection).
any help on this matter would be great.LeAnne wrote:
> My application is developed in VB.NET and uses a single/same connection
> string for all users (all
> installations), which uses SQL Server 2000 as the data store.
> But I need to keep track of who inserted a record and who modified a recor
d
> (along with the dates and times).
> Using triggers, I can easily setup the dates when a record was added and
> last modified.
> But how can i determine what users is connected to the application and is
> making the changes.
> As this information is stored in the application.
> Is there a way that i can inform SQL what user is connecting to the server
> (database), or set this information in SQL Server such that this informati
on
> is stored for each session. I.e. is there some technology similar to sessi
on
> states in websites in Sql server. where i can store session variables for
> each session on the website, thus enabling me to store session variables i
n
> sql server for each session (connection).
> any help on this matter would be great.
Just pass in the user name or ID as a parameter to the stored procedure
that performs the insert or update. In a two-tier application procs
should certainly be used for all data access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
There are SYSTEM_USER,HOST_NAME() ,USER_NAME ...system functions
If I remember well you can specify a workstationname within a connection
string to identify an user.
Anne" <Anne@.bogusemail.com> wrote in message
news:eBbc$FpRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> My application is developed in VB.NET and uses a single/same connection
> string for all users (all
> installations), which uses SQL Server 2000 as the data store.
> But I need to keep track of who inserted a record and who modified a
> record
> (along with the dates and times).
> Using triggers, I can easily setup the dates when a record was added and
> last modified.
> But how can i determine what users is connected to the application and is
> making the changes.
> As this information is stored in the application.
> Is there a way that i can inform SQL what user is connecting to the server
> (database), or set this information in SQL Server such that this
> information
> is stored for each session. I.e. is there some technology similar to
> session
> states in websites in Sql server. where i can store session variables for
> each session on the website, thus enabling me to store session variables
> in
> sql server for each session (connection).
> any help on this matter would be great.
>
>