quote:

---

Originally posted by Bustaz Kool

Are you having the application leave the SQL connection open all of the time? If not, couldn't your spid change every time you re-connect?

You seem to have a User table. Could you pass the primary key of this table to identify the user.

There are some builtin functions, like USER, USER_NAME, and others that might be relevant to your problem. Look in BOL for details.

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

---

quote:

---

Originally posted by TG

spid would not be a reliable candidate to associate with users since a spid is just a sql server process id. The application has no control over maintaining a consistent spid by user, especially for auditing over time when the connections will be broken and apps need to be restarted, connection pooling is implemented, etc. There are many ways to track usage with a custom authentication scheme. Typically, when a user "logs in" the userid (or authentication token of some sort) is captured and cached. The token is then sent along with the requests and can be used for column/row level entitlements, application object entitilements, auditing, etc. Also typically, each request would be only associated with a user or perhaps a user/session, but not by the internal server process id.

Be One with the Optimizer
TG

---

set nocount on
create table MyTable (MyTableID int identity(1,1), col1 int)
create table MyTableAudit (MyTableAuditID int identity(1,1), MyTableID int, Action char(1), oldCol1 int, newCol1 int, ChangedByUserID int, ChangedDate dateTime)
GO

create trigger tr_insMyTable ON MyTable for insert, update
as

--Get UserID from Context_Info column of sysProcesses 
--(context_info must be set by insert or update SP)
declare @userid int
select @userid = convert(int,convert(binary(4), context_info)) from sysProcesses where spid = @@spid

insert myTableAudit (MyTableID, Action, oldcol1, newcol1, ChangedByUserID, ChangedDate)
Select	i.MyTableID
	,case when d.MyTableID is NULL then 'I' else 'U' End
	,d.col1
	,i.col1
	,@userid
	,getDate()
From	inserted i
left JOIN deleted d ON i.MyTableID = d.MyTableID

GO

create proc insMyTable
@userid int
,@col1 int
as 

--set context_info to userid
declare @user_CI varbinary(128)
set @user_CI = convert(varbinary(128),@UserID)
set Context_Info @user_CI

insert myTable (col1)
values (@col1)

GO
create proc updMyTable
@myTableID int
,@userID int
,@col1 int
as

--set context_info to userid
declare @user_CI varbinary(128)
set @user_CI = convert(varbinary(128),@UserID)
set Context_Info @user_CI

update MyTable set col1 = @col1 where myTableID = @myTableID
GO

exec insMyTable @userid = 15, @col1 = 12345
exec insMyTable @userid = 15, @col1 = 100
exec updMyTable @myTableID = 1, @userID = 25, @col1 = 7890
Select * from MyTable
Select * from MyTableAudit

GO
drop proc insMyTable
drop proc updMyTable
drop table myTableAudit
drop table myTable

quote:

---

Originally posted by TG

>>Could it have something to do with connection pooling?
Certainly could. depends on your architecture (sql Provider, web app, windows app, client server/multi-tier)
The point is it's not worth figuring out because it will change without the app knowing about it.

>>AFAIK trigger can only use data from the table and cannot accept any parameters.
true. We've done it a couple ways. Tables we audited had 4 columns (CreatedBy CreatedDate, LastUpdatedBy, UpdateDAte) Those columns were used by the triggers for insert/update. The other way we've done it is to only change tables via their Insert, Update, and Delete SPs. The SPs controlled auditing as well as other business rules.

I also remember a topic on this forum where someone was using Context_Info column of sysProcesses table to relate current spid with spid's user. I'll look for it and post the topic_ID if I find it.

EDIT:
found it: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47048

using context_info is possible however, it still depends on the context_info being set by the current session prior to performing the insert/update/delete. In other words, adhoc changes to the table won't necessarily capture who made the changes. Also, this stategy requires the additional steps of setting and getting the context_info (from sysprocesses).

My preference is keeping the extra columns in the tables you want to audit. That way adhoc changes are audited correctly and you report that info to the user as part of the main data without having to make special trip to audit table.

Here is my tesing of context_info (in case you're interested)

set nocount on
create table MyTable (MyTableID int identity(1,1), col1 int)
create table MyTableAudit (MyTableAuditID int identity(1,1), MyTableID int, Action char(1), oldCol1 int, newCol1 int, ChangedByUserID int, ChangedDate dateTime)
GO

create trigger tr_insMyTable ON MyTable for insert, update
as

--Get UserID from Context_Info column of sysProcesses 
--(context_info must be set by insert or update SP)
declare @userid int
select @userid = convert(int,convert(binary(4), context_info)) from sysProcesses where spid = @@spid

insert myTableAudit (MyTableID, Action, oldcol1, newcol1, ChangedByUserID, ChangedDate)
Select	i.MyTableID
	,case when d.MyTableID is NULL then 'I' else 'U' End
	,d.col1
	,i.col1
	,@userid
	,getDate()
From	inserted i
left JOIN deleted d ON i.MyTableID = d.MyTableID

GO

create proc insMyTable
@userid int
,@col1 int
as 

--set context_info to userid
declare @user_CI varbinary(128)
set @user_CI = convert(varbinary(128),@UserID)
set Context_Info @user_CI

insert myTable (col1)
values (@col1)

GO
create proc updMyTable
@myTableID int
,@userID int
,@col1 int
as

--set context_info to userid
declare @user_CI varbinary(128)
set @user_CI = convert(varbinary(128),@UserID)
set Context_Info @user_CI

update MyTable set col1 = @col1 where myTableID = @myTableID
GO

exec insMyTable @userid = 15, @col1 = 12345
exec insMyTable @userid = 15, @col1 = 100
exec updMyTable @myTableID = 1, @userID = 25, @col1 = 7890
Select * from MyTable
Select * from MyTableAudit

GO
drop proc insMyTable
drop proc updMyTable
drop table myTableAudit
drop table myTable

Be One with the Optimizer
TG

---

quote:

---

Originally posted by TG

>The context_info method needs spid too which seems to keep changing.
I'm not pushing this idea, but for the solution I provided, the spid doesn't change between the call to the sp and the firing of the trigger. It can't change because it happens in the same process. It uses the spid but only as a means to communicate the context (specific instance of the sp firing) to the trigger. The UserID is what is stored in the context_info column and posted to the audit table. This solution satisfies your requirements.

The only problem I have with it is that I've never heard of anyone doing it before. I'm not sure what would happen under heavy contention.

>>What about using host_name? Wouldn't that be a good idea?
Well, that would make more sense than spid. Host_Name is just a machine identifier. When user's get a new machine, you will loose audit continuity. Also if a user logs into another machine, the host_name will not follow the user around. The readers of the audit tables will inacurately believe the normal operator of that machine made the modifications.

>>will increase the size of the database drastically since I have several transaction tables.
Disc space is pretty cheap these days :)

Be One with the Optimizer
TG

---