Using SPID to identify user

Question

Using SPID to identify user

GaryMcAllister

Mr or Mrs. 500

Points: 521
More actions
September 2, 2009 at 4:12 am

#141340

Howdy All,
I have an application which has an SPID to user map. Whenever a user begins a transaction the current SPID is mapped to the connecting user via stored proc (ran at Data Access Layer) and deletes all previous associated SPIDs.
I then have a UDF which then obtains the username from the SPID.
The issue is when my audit trigger fire on the entities sometimes an invalid user is being entered into the table (i.e. the user which didn't submit the transaction). The system supports 100s of concurrent users.
This leads me to the opinion that the trigger is firing after disconnection and the spid is being re-used.
This may not be best practise for this kind of app and I am happy to refactor the solution, but maybe someone could help me userstand the SPID allocation process as I was of the impression disconnection doesn't happen until triggers are complete ?
Thanks
Gary

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 1

The trigger will be executed within the same context and transaction the DML that caused the trigger to fire. There must be something else happening.....

Clear Sky SQL
My Blog[/url]

GaryMcAllister Mr or Mrs. 500 Points: 521 More actions · Answer 2

Hmmmmm... maybe on occassions the spid->username map fails to write and the exception handing is allowing future transactions to continue..

I'll do some further digging..

Is there something fundamentally wrong with what I am doing?

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 3

GaryMcAllister (9/2/2009)
Hmmmmm... maybe on occassions the spid->username map fails to write and the exception handing is allowing future transactions to continue..
I'll do some further digging..
Is there something fundamentally wrong with what I am doing?

Not sure about 'wrong' but when you say "current SPID is mapped to the connecting user via stored proc", would this not be better by using master..sysprocesses ?

Clear Sky SQL
My Blog[/url]

GaryMcAllister Mr or Mrs. 500 Points: 521 More actions · Answer 4

My schema works like so....

UserMap = GUID, SPID, AppUsername

When a connection is established to the db.

Conn C = new Conn();

C.Execute("DELETE FROM UserMap WHERE SPID = @@SPID");

C.Execute("INSERT INTO UserMap (SPID,AppUsername) VALUES (@@SPID, 'gmcallister')");

.... Do more transactions.

I then have triggers on underlying entities which obtain the user based on SPID (SELECT Username FROM UserMap WHERE SPID=@@SPID).

My userstanding is that using the master database is not recommended due to the fact it requires escalated security.

Thanks for the help so far...