Roman Rehak
3/20/2007 2:33:00 PM
That is correct, each connection has a unique SPID and you should never see
two connections with the same SPID. The SPID # could be the same if the
connection is reused from the pool but the user context would be different.
Also, be aware that with Windows Authentication you get one connection pool
per user, not one pool per unique connection string as many believe.
--
Roman Rehak
SQL Server MVP
"Johan Karlsson" <johan.karlsson@caretech.se> wrote in message
news:OBPAbEsaHHA.4788@TK2MSFTNGP04.phx.gbl...
> Hi!
>
> Did I get this right?
>
> * One connection has one SPID (select @@SPID returns an integer that only
> is valid for the current connection).
> * No other process can share the same connection unless it is closed
> (con.closed) in which case it is returned to the connection pool.
>
> Something like this?
> con.open
> ''' @@SPID IS UNIQUE until closing the connection?
> con.close
>
> The background story is that I'm looking for a way to create an audit
> trail. I have the user id in the business layer and I'm investigating the
> possibility of using context_info (that in turn uses @@spid to identify
> the current context) for storing the userid. My concern is ofcourse if the
> userid "leaks" between connections somehow. :)
>
> Thanks
> // Johan
>