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


Usages of CONTEXT_INFO


Usages of CONTEXT_INFO

Author
Message
Tore Bostrup-382308
Tore Bostrup-382308
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 139
We 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).


Regards,

Tore Bostrup
Lisa Slater Nicholls
Lisa Slater Nicholls
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 631

>>As long as the trigger is enabled you can make sure that the logic will work.

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?

Great article, thank you...

>L<


Lisa Slater Nicholls
Lisa Slater Nicholls
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 631

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!).

Looking at the docs, it seems maybe you could use context info more pervasively in a system and then do something like this:

SELECT context_info AS MyCtxInfo

FROM sys.dm_exec_requests

WHERE session_id = @@SPID

AND request_id = CURRENT_REQUEST_ID();

... ? 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.

Anyone??

>L<


Rafiuddin Syed
Rafiuddin Syed
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 18
Very Good Article, I was not aware of it...
David NienDuy Nguyen
David NienDuy Nguyen
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 1

Very useful article ! I don't know about this until now.

Thank you. David N Nguyen.


Robert Davis
Robert Davis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10698 Visits: 1660

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).

I had to rewrite the trigger as following:

IF (Select CONTEXT_INFO()) <> Cast('uspModifyEmployees' as binary(128))

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:

IF (Select IsNull(CONTEXT_INFO(), Cast('' as binary(128)))) <> Cast('uspModifyEmployees' as binary(128))

Of course, this doesn't really stop me from doing ad hoc queries, as I can still do this:

Set Context_Info 0x7573704D6F64696679456D706C6F79656573

Delete From Employees

Where EmpID = 1

SET CONTEXT_INFO 0x0




My blog: SQL Soldier
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management
Merrill Aldrich
Merrill Aldrich
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 137
Interesting, 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.
Robert Davis
Robert Davis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10698 Visits: 1660
I should also add that I can disable or drop the trigger as well.


My blog: SQL Soldier
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management
Phil Taylor
Phil Taylor
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 75
I agree totally, in fact I go so far to say that this 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.
Vasant Raj
Vasant Raj
Say Hey Kid
Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)

Group: General Forum Members
Points: 703 Visits: 137

Informative article... i was not aware of the CONTEXT_INFO.

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.


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