question

user-470 avatar image
user-470 asked

How to modify permissions for SET CONTEXT_INFO?

I have an AFTER UPDATE trigger that does two things: * inserts some audit records * modifies a timestamp on the updated table (thus triggering another update) In order to avoid a circular loop, I have the following in the trigger: CREATE TRIGGER Blah ON MainTable BEGIN declare @Context varbinary(128) SELECT @Context = CONTEXT_INFO() IF (@Context = 0x01) RETURN ... INSERT INTO AuditTable ... SET CONTEXT_INFO = 0x01 UPDATE MainTable SET WhenLastModified = GetDate() SET CONTEXT_INFO = 0x END However, this means that someone could potentially bypass auditing completely simply by setting the CONTEXT_INFO before there own modifications. Is there any way to restrict permissions on SET CONTEXT_INFO, so that it can only be used from within a trigger?
sql-server-2008triggerpermissions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Usman Butt avatar image
Usman Butt answered
I guess [TRIGGER_NESTLEVEL()][1] could be more helpful then CONTEXT_INFO() in this case. You can pass the objectid of the trigger to check the nested level of this specific trigger. Another option could be to use INSTEAD OF trigger. Hope it helps. [1]: http://msdn.microsoft.com/en-us/library/ms182737%28v=sql.100%29.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The INSTEAD OF trigger isn't an option because of cascading foreign keys. However, the TRIGGER_NESTLEVEL() with object_id looks perfect for my case. Thanks!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.