SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Usages of CONTEXT_INFO


Usages of CONTEXT_INFO

Author
Message
Rohit D
Rohit D
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 7

>> This may destroy the ability to use connection pooling. Then each user will have there

>> own connections, increasing resources on the both the db server and web (or app) server.

>> Scalability and performance of your site will likely decrease. Or, if connection pooling still

>> works when using context_info, then you will run the risk of having the Context_info reset

>> on the connection, or being used by the wrong user. In an ASP app, there is no guarantee

>> that you will get the same connection each time. And, other users will share the same

>> connection.

Serving the Multilingual data with concept of ContextInfo technique does not destroy the ability of Connection Poolling. The extra overhead which comes is that every command has to precede with a Set ContextInfo call based on the User's context.

The connection Reset will not hamper the logic as the next call to the DB will precede again with a SetContextInfo call and hence different Users can share the same connection.


Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6504 Visits: 1632

Of course, I don't think the author's intention was to claim that Context_Info was the solution to all of our security concerns. I think he merely chose that as an example of how to use it.

I wonder if you can change Context_Info repeatedly within a stored. Perhaps you could use it to output debugging info or something like that. Don't you hate when you have debugging statements in a procedure, but they don't get output until the procedure completes or fails? Maybe this can be used to output some debugging info as it runs. I don't know, just a thought.





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56697 Visits: 9730
Robert Davis (12/22/2006)


Of course, I don't think the author's intention was to claim that Context_Info was the solution to all of our security concerns. I think he merely chose that as an example of how to use it.

I wonder if you can change Context_Info repeatedly within a stored. Perhaps you could use it to output debugging info or something like that. Don't you hate when you have debugging statements in a procedure, but they don't get output until the procedure completes or fails? Maybe this can be used to output some debugging info as it runs. I don't know, just a thought.


If you use "raiserror('your debug statement', 10, 1) with nowait", you get the error messages in SSMS/QA immediately, at that step in the process. You can use a varchar/nvarchar variable for the debug statement, so you can even put a timestamp in it, or other data (variable values, for example). The key is the "nowait" hint. Severity 10 is used for informational messages and won't interrupt the flow of the script/proc.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
David Korb
David Korb
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 311
I have several layers of nested procedures and when it reached trigger time, @@spid returned the top executing sproc rather than the update sproc, so it failed out. The only way I could make it work was to add a context_info check in the trigger against every procedure that might be at the top of the hierarchy.

Of course, this allows direct upserts in any procedure along the hierarchy, but at least it protects the table from business analysts with sysadmin rights who use "open table" all the time.

Having standards (like good security) is nice if you can enforce them, but my shop exists somewhere in the wild west!

Here's my latest implementation within the trigger:
if exists (
select *
from sys.dm_exec_sessions as SYS
where not exists (
select *
from dbo.v_lookup_context_info as sub
where sub.context_info_as_varchar
= cast( SYS.context_info as varchar )
this_is_a_close_paren_that_turned_into_an_emoticon
and session_id = @@spid
)
begin
raiserror('You can not write to XXXX outside of XXXX',16,1)
return
end
tymberwyld
tymberwyld
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 275
This is a very interesting technique. I am using CONTEXT_INFO to set the current "ClientContext" that is executing any Procedures from an Application. This allows me to track who is retrieving data as well as doing simple things like converting all DateTime values to the client's current TimeZoneOffset. My ClientContext parameter of my Procs is sent in via Xml, converted to a simplier String (VarChar) representation to make it shorter, then set on the CONTEXT_INFO.

No, CONTEXT_INFO has no affect on Connection Pooling. This happens AFTER a connection is made and is not part of the ConnectionString anyway. For those of you who've never watched a connection-pooled App through Profiler, you would see that basically the Pool instantiates a number of Connections on you behalf, so, "each user having their own connection" has ALWAYS been the way pooling works. Until MARS came along, you couldn't even execute multiple Readers on the same connection at the same time.

Also, you should probably not be querying sys.dm_exec_sessions, sys.dm_exec_requests, or sys.sysprocesses directly becuase you probably won't have the correct permissions to do that in Production code. Instead, query you Context this way "SELECT CONTEXT_INFO()".



David Korb
David Korb
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 311
Also, you should probably not be querying sys.dm_exec_sessions, sys.dm_exec_requests, or sys.sysprocesses directly becuase you probably won't have the correct permissions.


Maybe that's why I never got it to work properly, but at the time it seemed to be trouble with nested stored procedures where the wrong value persisted during execution of child procedures. It might be easier to roll your own by creating a permissions table and using triggers and OBJECT_NAME( @@PROCID ) to validate that the data manipulation is happening according to your rules.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search