December 3, 2008 at 7:44 pm
Is there a way to change database context in a trigger?
For example. I want to use a login trigger to open a symetric key for decryption/ encryption in a session but the
open symetric key can't find the key I have created. I assume it is because it is thinking the context is the master database.
I am thinking I will have to implement the service broker which if it work is not a problem because I am going to have to write a logout trigger with it to close the key any (because SQL does not have logout triggers -grrrr!)
December 8, 2008 at 11:34 am
bodhilove (12/3/2008)
Is there a way to change database context in a trigger?For example. I want to use a login trigger to open a symetric key for decryption/ encryption in a session but the
open symetric key can't find the key I have created. I assume it is because it is thinking the context is the master database.
I am thinking I will have to implement the service broker which if it work is not a problem because I am going to have to write a logout trigger with it to close the key any (because SQL does not have logout triggers -grrrr!)
Fascinating question! It never ceases to amaze me how many times people put on blinders (myself included!) and decide that they have to solve a problem IN A PARTICULAR WAY, without asking what are the best tools to use to solve a problem.
Why would you expect SQL Server to handle encryption/decryption? It does data storage. So it will do a wonderful job of storing encrypted and decrypted values, but not do a great job of actually performing the operations of encrypting and decrypting USER data. Those tools are outside the purview of SQL Server (unless these are features I'm simply unaware of). Why else would Microsoft encourage the use of integrated logins?
Can you restate your question in terms of the objective problem you're trying to solve? Using a sledgehammer to swat a fly will give you lots of exercise and create a big mess, but probably not get the fly.
To try to address the question you've posed, a trigger starts within a set context (however you define context). If you want to 'reach outside' that context, it's possible to do, provided that outside context can be reached (elsewhere postings describe attempts to use SQL Server IDs instead of domain IDs give counterexamples where security rules can't be stretched far enough). But without knowing what you mean, it's hard to give a meaningful answer, unless I've been lucky and nailed it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply