Russell Fields
3/22/2007 6:10:00 PM
Johan,
You would issue this with each batch of SQL to make sure that the current
batch has the proper setting.
So, connect, then issue batch:
SET CONTEXT_INFO binaryvalue
EXEC MyProcThatDoesTheWork
EXEC MyNextProc
Then the Context_Info is set for the lifetime of whatever that batch is
doing.
RLF
"Johan Karlsson" <johan.karlsson@caretech.se> wrote in message
news:%23e5ny2YaHHA.808@TK2MSFTNGP04.phx.gbl...
> Hi!
>
> That sounds interesting. A follow up question though. How does the
> presence of a connectionpool affect this? A connection is not reused until
> someone closes it right?
>
> Thanks
> // Johan
>
>
> "Russell Fields" <russellfields@nomail.com> wrote in message
> news:OCYdUB$ZHHA.2448@TK2MSFTNGP02.phx.gbl...
>> Johan,
>>
>> If your Business Tier knows who called it, once it makes a connection it
>> can stuff the users login account into Context_Info, then call the stored
>> procedure.
>>
>> The stored procedure can then fetch that value for use:
>> SQL Server 2000 - SELECT context_info FROM sysprocesses WHERE spid =
>> @@SPID
>> SQL Server 2005 - SELECT CONTEXT_INFO ( )
>>
>> RLF
>>
>> "Johan Karlsson" <johan.karlsson@caretech.se> wrote in message
>> news:ul8bkn8ZHHA.1508@TK2MSFTNGP06.phx.gbl...
>>> Hi!
>>>
>>> Thank you for your reply!
>>>
>>> The SPID (server process id?) is unique for the connection right? It
>>> doesn't really help me since I can't associate it my custom user id
>>> unless I know the SPID before executing the query?
>>>
>>> Thanks
>>> // Johan
>>>
>>> "Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message
>>> news:eJkNvM8ZHHA.1216@TK2MSFTNGP03.phx.gbl...
>>>> Hello,
>>>>
>>>> For getting the SPID use below:-
>>>>
>>>> select @@SPID
>>>>
>>>> For getting the userid execute:-
>>>> select user_ID()
>>>>
>>>> Thanks
>>>>
>>>> Hari
>>>>
>>>> "Johan Karlsson" <johan.karlsson@caretech.se> wrote in message
>>>> news:OH8NuG8ZHHA.4008@TK2MSFTNGP05.phx.gbl...
>>>>> Hi!
>>>>>
>>>>> We have a system where all authentication is handled in the Business
>>>>> Tier. The database doesn't really know who's calling except that it is
>>>>> a certain winservice and whatever user it's running under. Is there
>>>>> anyway to attach somekind of metadata along with the db-call that
>>>>> could be read by a trigger? Perhaps to the connectionobject?
>>>>>
>>>>> I want to use this as an audit function so it has the be only for the
>>>>> given call or at least stay the same for the entire call.
>>>>>
>>>>> In the database, I need to read the value from inside a trigger in
>>>>> order to mark changed rows with my custom user id?
>>>>>
>>>>> Any pointers?
>>>>>
>>>>> Thanks
>>>>> // Johan
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>