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.

Usages of CONTEXT_INFO