We are thinking of moving to SqlServer 2005 and I have come across the
following problem and I am looking for a "best practices" solution to
it. Currently we access all of our data through stored procedures. Any
auditing we do happens on the client side. We are now looking to add a
lot more auditing and the database seems like the place to do it. We do
not want to modify all our stored procs to add username to it. Our
connection strings do not use the actual user name, we have functional
usernames that certain parts of our systems use to login to the
database. For example, the Notifications subsystem always logs into the
database as "NotificationEntity". We do have a centralized data access
layer so if we had to do something special to pass in the ADS user
(real or impersonated) we could. We were thinking of using triggers to
write to journal tables that look just like our original tables with
the addition of user name and timestamp. Our only issue is on how to
set the username in the stored proc call so that it's available to the
trigger. Our client data access layer is written in C# and it works
both in .Net 1.1 and 2.0.

Thanks

<DIV>&lt;cost***@gmail.com&gt; wrote in message
thinking of moving to SqlServer 2005 and I have come across the
Show quoteHide quoteJust stuff the "real" user name in a table keyed by the session id (SPID),
and read it from there in your triggers, procedures or views.

create table SessionData(SPID int primary key, UserName varchar(50))


create procedure SetSessionData(@UserName varchar(50))
as
begin
  set nocount on
  update SessionData set UserName = @UserName where SPID = @@SPID
  if @@rowcount = 0
  begin
    insert into SessionData(SPID,UserName) values (@@SPID,@UserName)
  end
end


Then in a proc or trigger

  declare @UserName varchar(50)
  select @UserName = UserName from SessionData where SPID = @@SPID



David

Best way to insert data into tables without primary keys
What is new in Sql Server 2005 cursors?
error converting datatype varchar to numeric
Update Query in SQL 2005 with inner join
nesting select statements