Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Usages of CONTEXT_INFO

By Yousef Ekhtiari,

Usages of CONTEXT_INFO

At my work place we needed to make sure that one of our table will never be modified except via a stored procedure. However for some reasons we did not want to implement the security plan for that table. What can we do if someone intentionally or unintentionally modified the table directly?

I remembered that I read somewhere about CONTEXT_INFO , and I accepted the work. After some investigation, I found the answer and I decided to share it with you. Consider the following table which we want to manipulate it just within a stored procedure:

 
CREATE TABLE Employees
(
  empid   INT       PRIMARY KEY  NOT NULL,
  empname VARCHAR(25) NOT NULL,
)
From BOL: SET CONTEXT_INFO Associates up to 128 bytes of binary information with the current session or connection.

 
select cast('xxxxx' as varbinary(128))
I replace xxxxx with my sp's name
 
select cast('uspModifyEmployees' as varbinary(128))
If you run the above code you get the following result:
0x7573704D6F64696679456D706C6F79656573
Now to decode the binary value run:
 
select cast (0x7573704D6F64696679456D706C6F79656573 as varchar(128))

WOW ,the result is really fantastic. So put SET CONTEXT_INFO 0x7573704D6F64696679456D706C6F79656573 at the beginning of your stored procedure and SET CONTEXT_INFO 0x0 at the end of the sp.

 
create proc uspModifyEmployees (
	@action char(1),
	@empid   INT   ,
	@empname VARCHAR(25)=NULL
	
)
AS
SET NOCOUNT ON
SET CONTEXT_INFO 0x7573704D6F64696679456D706C6F79656573 
IF @action='I'
   INSERT Employees(empid,empname) VALUES(@empid,@empname)
ELSE IF @action='D'
   DELETE Employees WHERE empid=@empid
ELSE IF  @action='U'
   UPDATE Employees SET empname=@empname WHERE empid=@empid
ELSE 
   RAISERROR('Unkown action',16,1)

SET CONTEXT_INFO 0x0 
/*NOTE: if you forget to reset the CONTEXT_INFO at the end, it is possible to manipulate the table outside the sp since the value you set for CONTEXT_INFO will remain until the connection is closed*/

GO

CREATE  TRIGGER trg_Employees_iud on Employees
AFTER INSERT,DELETE,UPDATE
AS
IF @@ROWCOUNT=0 RETURN
--If you use SQL Server 2005 it is better to replace dbo with sys 
IF (SELECT CAST(CONTEXT_INFO AS VARCHAR)FROM master.dbo.SYSPROCESSES WHERE SPID=@@SPID) <>'uspModifyEmployees'
BEGIN
	

	RAISERROR('You can not modify  Employees''s table outside of uspModifyEmployees''s procedure ',16,1)
	ROLLBACK TRAN
	RETURN
END 
Let's insert some data into our employee's table by stored procedure
 
exec uspModifyEmployees @action ='I',@empid   =1 ,@empname= 'John'
exec uspModifyEmployees @action ='I',@empid   =2 ,@empname= 'Joshua'
exec uspModifyEmployees @action ='I',@empid   =3 ,@empname= 'George'
To check the result uses the following code:
 
select * from  Employees
here is the result:
 
empid       empname                   
----------- ------------------------- 
1           John
2           Joshua
3           George

(3 row(s) affected)
It's time to modify the data. to check the Delete & Update behavior, run the following codes:
 
exec uspModifyEmployees @action ='D',@empid=1 
exec uspModifyEmployees @action ='U',@empid=2, @empname='Hassan'
Again to make sure that all modifications have performed correctly, run the following code:
 
SELECT * FROM  Employees
here is the result:
 
empid       empname                   
----------- ------------------------- 
2           Hassan
3           George

(2 row(s) affected)
Now let's manipulate data directly against the table
 
INSERT Employees(empid,empname) VALUES(4,'Kevin')

You'll get the following error depending on which environment you have used:
SQL Server 2000 :

Server: Msg 50000, Level 16, State 1, Procedure trg_Employees_iud, Line 9
You can not modify Employees's table outside of uspModifyEmployees's procedure

SQL Server 2005 :
.Net SqlClient Data Provider: Msg 50000, Level 16, State 1, Procedure trg_Employees_iud, Line 9
You can not modify Employees's table outside of uspModifyEmployees's procedure
.Net SqlClient Data Provider: Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

Update and delete actions:

 
  DELETE Employees WHERE empid=2
  UPDATE Employees SET empname='Walter' WHERE empid=2
The same errors will generate and whole transaction will roll back. If you try to do those actions within Enterprise Manager in SQL Server 2000 or Management Studio in SQL 2005 ,depending on your environment, you will get the following messages:

Enterprise Manager

Management Studio

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

Another scenario which this technique might be handy

You want to find how many separate users have logged into your server via ASP pages, but how? I bet you all answer Performance Monitor counter SQL server:general statistics.User connection. It only shows number of connections not number of actual users that means each user may have several connections on the server. You execute the following code after opening the connections :

 
DECLARE @s VARBINARY (128)
SET @s =CAST('Username'AS  VARBINARY(128))
SET CONTEXT_INFO @s
And on your SQL editor run this code:
 
WITH MYcte(username,gp,noconn)
AS  (
SELECT 
	CAST(CONTEXT_INFO AS VARCHAR(128)),
	GROUPING(CAST(CONTEXT_INFO AS VARCHAR(128))),
	COUNT(*) 
FROM 
	sys.sysprocesses
GROUP BY CAST(CONTEXT_INFO AS VARCHAR(128))WITH ROLLUP
)

SELECT   username= CASE gp  
		     WHEN  1 THEN 'Total connections' 
		     ELSE username 
                  END ,
	noconn
FROM MYcte;

I always prefer to use new things, so I used a common table expression to find the final result.

Conclusion:

You can memorize this technique until it becomes handy. There are several times when you learn a new thing, but you can not use it immediately. In a special situation suddenly you remember the tip and then you can take the advantage of it.

Total article views: 11361 | Views in the last 30 days: 55
 
Related Articles
FORUM

select [Employee_ID] = EmpID from dbo.Employee GROUP BY [Employee_ID]

select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID] --Group by error

FORUM

how to do sum the salarie of employes if empnames are equal in ssis

if empnames in a table are equal then that particular employees salary should be add

FORUM

Employee job mapping

Soln required for employee and job mapping query

ARTICLE

Selecting from hierarchies like Managers and Employees

Chuck Hoffman shows a technique using sets for selecting records from hierarchies such as Manager / ...

FORUM

sql query need Help about Absent Employee

Query about absent employee

Tags
sql server 2005    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones