SQLServerCentral.com / Discuss Content Posted by Yousef Ekhtiari / Article Discussions / Article Discussions by Author / Usages of CONTEXT_INFO / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 12:36:40 GMT20RE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx[quote]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.[/quote]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.Fri, 02 Jan 2009 11:35:10 GMTDavid KorbRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxThis 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()".Tue, 23 Dec 2008 09:40:50 GMTtymberwyldRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxI 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) returnendThu, 14 Aug 2008 00:01:21 GMTDavid KorbRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx[quote][b]Robert Davis (12/22/2006)[/b][hr]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.[/quote]If you use "raiserror('[i]your debug statement[/i]', 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.Tue, 18 Dec 2007 07:12:38 GMTGSquaredRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>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.</P><P>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.</P>Fri, 22 Dec 2006 02:21:00 GMTRobert DavisRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>&gt;&gt; This may destroy the ability to use connection pooling. Then each user will have there </P><P>&gt;&gt; own connections, increasing resources on the both the db server and web (or app) server. </P><P>&gt;&gt; Scalability and performance of your site will likely decrease. Or, if connection pooling still </P><P>&gt;&gt; works when using context_info, then you will run the risk of having the Context_info reset </P><P>&gt;&gt; on the connection, or being used by the wrong user. In an ASP app, there is no guarantee </P><P>&gt;&gt; that you will get the same connection each time. And, other users will share the same </P><P>&gt;&gt; connection.</P><P>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. </P><P>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.</P><P> </P>Fri, 22 Dec 2006 00:20:00 GMTRohit DRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>Informative article... i was not aware of the CONTEXT_INFO.</P><P>I feel that use of CONTEXT_INFO in the scripts will be creating dependencies with the developers i.e., it will be mandatory to use this in each script. Setting up the permissions will be the correct and secure option.</P>Thu, 21 Dec 2006 23:44:00 GMTVasant RajRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxI agree totally, in fact I go so far to say that t<FONT size=2>his article is going to confuse a lot of people new to SQL Server. What the author is proposing really should be done through permissions. Simply no permissions should be granted to the table directly and execute access granted to the stored procedure itself. I'm not sure if the article was intended as an academic exercise but it seems fairly impractical approach to me.</FONT>Thu, 21 Dec 2006 18:39:00 GMTPhil TaylorRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxI should also add that I can disable or drop the trigger as well.Thu, 21 Dec 2006 13:30:00 GMTRobert DavisRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxInteresting, but please all do not forget that this is what PERMISSIONS are for in SQL Server. I find it's best to use a thing according to its intended purpose first, and only tweak/hack it when absolutely necessary. This trick is not as secure, it's harder to understand, it would potentially make life miserable for someone working on the system who is unfamiliar with it, etc. etc.If you want a table to be read only, then:1. Set permissions for the user(s) or role(s) as Select for the table 2. Set permissions to grant execute on the stored proc.This works perfectly.Thu, 21 Dec 2006 12:53:00 GMTMerrill AldrichRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>This procedure wouldn't work for me in SQL 2005. Casting the Context_Info as varchar did not work. I had to cast the string as binary(128).</P><P> </P><P>I had to rewrite the trigger as following:</P><FONT color=#0000ff size=2><FONT color=#0000ff size=2><FONT color=#0000ff size=2><P>IF</FONT><FONT color=#000000 size=2> </FONT><FONT color=#808080 size=2>(</FONT><FONT color=#0000ff size=2>Select</FONT><FONT color=#000000 size=2> CONTEXT_INFO</FONT><FONT color=#808080 size=2>())</FONT><FONT color=#000000 size=2> </FONT><FONT color=#808080 size=2>&lt;&gt;</FONT><FONT color=#000000 size=2> </FONT><FONT color=#ff00ff size=2>Cast</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'uspModifyEmployees'</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000ff size=2>as</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000ff size=2>binary</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#000000 size=2>128</FONT><FONT color=#808080 size=2>))</P></FONT></FONT><P><FONT color=#808080 size=2><FONT color=#111111>Also, in SQL 2005, Context_Info can be Null which it is for an ad hoc query. A Null Context_Info would not be caught by the trigger since Null is neither equal nor not equal to anything. So, I further had to modify it to account for null:</FONT></FONT></P><FONT color=#808080 size=2><FONT color=#111111><FONT color=#0000ff size=2><P>IF</FONT><FONT color=#000000 size=2> </FONT><FONT color=#808080 size=2>(</FONT><FONT color=#0000ff size=2>Select</FONT><FONT color=#000000 size=2> </FONT><FONT color=#ff00ff size=2>IsNull</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#000000 size=2>CONTEXT_INFO</FONT><FONT color=#808080 size=2>(),</FONT><FONT color=#000000 size=2> </FONT><FONT color=#ff00ff size=2>Cast</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>''</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000ff size=2>as</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000ff size=2>binary</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#000000 size=2>128</FONT><FONT color=#808080 size=2>))))</FONT><FONT color=#000000 size=2> </FONT><FONT color=#808080 size=2>&lt;&gt;</FONT><FONT color=#000000 size=2> </FONT><FONT color=#ff00ff size=2>Cast</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'uspModifyEmployees'</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000ff size=2>as</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000ff size=2>binary</FONT><FONT color=#808080 size=2>(</FONT><FONT color=#000000 size=2>128</FONT><FONT color=#808080 size=2>))</FONT></P><P><FONT color=#111111 size=2>Of course, this doesn't really stop me from doing ad hoc queries, as I can still do this:</FONT></P><FONT color=#808080 size=2><FONT color=#0000ff size=2><P>Set</FONT><FONT size=2><FONT color=#000000> Context_Info 0x7573704D6F64696679456D706C6F79656573 </FONT></P></FONT><FONT color=#0000ff size=2><P>Delete</FONT><FONT color=#000000 size=2> </FONT><FONT color=#0000ff size=2>From</FONT><FONT size=2><FONT color=#000000> Employees</FONT></P></FONT><FONT color=#0000ff size=2><P>Where</FONT><FONT color=#000000 size=2> EmpID </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2><FONT color=#000000> 1</FONT></P></FONT><FONT color=#0000ff size=2><P>SET</FONT><FONT size=2><FONT color=#000000> CONTEXT_INFO 0x0 </FONT></P></FONT></FONT></FONT></FONT></FONT>Thu, 21 Dec 2006 12:20:00 GMTRobert DavisRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>Very useful article ! I don't know about this until now.</P><P>Thank you. David N Nguyen.</P>Thu, 21 Dec 2006 11:46:00 GMTDavid NienDuy NguyenRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxVery Good Article, I was not aware of it... <img src='images/emotions/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>Thu, 21 Dec 2006 10:30:00 GMTRafiuddin SyedRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>I thought maybe we could do this with EVENTINFO() in a trigger, but the calling proc isn't included in the schema (would be nice!).</P><P>Looking at the docs, it seems maybe you could use context info more pervasively in a system and then do something like this:</P><P><FONT color=#0000ff size=2>SELECT</FONT><FONT size=2> context_info </FONT><FONT color=#0000ff size=2>AS</FONT><FONT size=2> MyCtxInfo</P></FONT><FONT color=#0000ff size=2><P>FROM</FONT><FONT size=2> </FONT><FONT color=#008000 size=2>sys.dm_exec_requests</P></FONT><FONT color=#0000ff size=2><P>WHERE</FONT><FONT size=2> session_id </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>@@SPID</P></FONT><FONT size=2><P></FONT><FONT color=#808080 size=2>AND</FONT><FONT size=2> request_id </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> CURRENT_REQUEST_ID</FONT><FONT color=#808080 size=2>();</FONT></P><P>... ? but I am not sure, I don't understand how CURRENT_REQUEST_ID() works or whether you will ever see more than one result from the above code. </P><P>Anyone??</P><P>&gt;L&lt;</P>Thu, 21 Dec 2006 10:05:00 GMTLisa Slater NichollsRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>&gt;&gt;As long as the trigger is enabled you can make sure that the logic will work.</P><P>Maybe could also create a database-level trigger using the same technique, to make sure that the table wasn't altered or dropped outside of an approved way, to prevent this?</P><P>Great article, thank you...</P><P>&gt;L&lt;</P>Thu, 21 Dec 2006 09:34:00 GMTLisa Slater NichollsRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxWe are using the technique with connection pooling (although a WinForms app) for user/application context information required for logging purposes (used in triggers). By always setting the CONTEXT_INFO as part of our connection logic, we do not have to worry about "leftovers" from previous users. But in any case, I believe the connection reset that takes place when reusing a connection in the pool clears the previous user's CONTEXT_INFO (but you should verify this yourself).Thu, 21 Dec 2006 09:28:00 GMTTore Bostrup-382308RE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>Intresting article. Thank you Yousef.</P><P>I have one concern using this in SQL 2000. If you need to make sure you reset the Context_Info at the end of the procedure and an error occurs before it reaches that line in the stored procedure, won't it fail to execute the reset command?</P>Thu, 21 Dec 2006 09:04:00 GMTEd ThompsonRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxHello It is really useful..since long i was in search of read only tables.. I think this is one of the better way.I followed all the steps that you have mentioned in your article, but i did not create any trigger.as i dont want to use triggers.what should be result according to you? Is there any other way without using triggers Thu, 21 Dec 2006 08:06:00 GMTDeepa GheewalaRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>Yep, its neat, and I wasn't aware of it. Does anyone on here know if there is a way (other than using this), to extract the calling stored procedure in a trigger? What would be nice if there was a way in 2000 or 2005 to be able to determine in a trigger the procedure name (or just if it was a batch) that invoked that trigger. Would be great for auditing purposes.</P><P>Tim</P>Thu, 21 Dec 2006 07:37:00 GMTTim Chapman-218780RE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxWow, that's a pretty useful tip, something I never knew about. As you said in your article, I don't really need to use it right now but I'll definitely keep it in the back of my mind for the future. Thanks for sharing!Thu, 21 Dec 2006 07:20:00 GMTTimothy-313907RE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxDoes anyone know if the CONTEXT_INFO can be tracked in Profiler traces?Thu, 21 Dec 2006 07:15:00 GMTChris RoesenerRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>Regarding using this technique in an ASP environment, I would perform some additional testing. I'm not sure how Context_info applies when using connection pooling, but either way would appear to have problems.</P><UL><LI>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.</LI><LI>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.</LI></UL><P>Even still, thanks for the article, Yousef. Especially for the first intended purpose, this appears to the an elegant solution to your problem of restricting updates.</P>Thu, 21 Dec 2006 05:46:00 GMTMark HarrRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>Very Nice usage of CONTEXT_INFO. thanks for sharing.</P><P>Have used CONTEXT_INFO for the Multilingual solution, where the clients call can come in for any language Data. Since the Connection pool is used to serve the data, before executing the SP we set the CONTEXT and within the SP, query the Sysprocesses to figure out the Language of the current SPID and serve the appropriate data.</P>Thu, 21 Dec 2006 03:11:00 GMTRohit DRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<FONT size=2><P>This is pretty cool... we have several complex, multi-step store procedures and some folks have put some pretty complex logging procedures in them just so they can query a table to see "how it's doing". With just a little forethought, you can add these simple lines to the proc and let the SysProcesses table take care of it…</P></FONT><DL><DT><FONT face="Courier New">SET @MyStatus = CAST('<EM>someprocname</EM> is at Step <EM>X</EM>' AS VARBINARY(128))</FONT></DT><DT><FONT face="Courier New">SET CONTEXT_INFO = @MyStatus</FONT></DT></DL><FONT size=2><P>With a bit more forethought, you could make a function that you pass the SPID to and it will automatically get the Context_Info from SysProcesses and decode it for readability.</P><P>If you "word" it correctly, you could even put a timestamp in the Context_Info and have a view decode when the step started, how long the step has been running, the proc name (can save some space by passing the ID of the proc contained in sysobjects), etc. Then, just select from the view... use a WHERE for spid if you want. Combine that with the other available columns such as Host_Name, etc, and you have some pretty good info.</P><P>Thanks, Yousef... nice tip.</P><P> </P></FONT>Wed, 20 Dec 2006 22:43:00 GMTJeff ModenRE: Usages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspx<P>How simple... how elegant... I never knew you could do this... nicely done.</P><P> </P>Wed, 20 Dec 2006 22:22:00 GMTJeff ModenUsages of CONTEXT_INFOhttp://www.sqlservercentral.com/Forums/Topic331105-336-1.aspxComments posted here are about the content posted at <A HREF="http://www.sqlservercentral.com/columnists/yEkhtiari/2765.asp">http://www.sqlservercentral.com/columnists/yEkhtiari/2765.asp</A>Sun, 17 Dec 2006 10:02:00 GMTyousef Ekhtiari