Click here to monitor SSC
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-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 135
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
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 610

>>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
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 610

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
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 18
Very Good Article, I was not aware of it...
David NienDuy Nguyen
David NienDuy Nguyen
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1

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

Thank you. David N Nguyen.


Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623

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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Merrill Aldrich
Merrill Aldrich
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

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



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
Phil Taylor
Phil Taylor
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 74
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
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 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