Trigger causing error "Server Principal is not able to access the database under the current secuirty context"
Posted on 2012-08-21
Just as the title describes.
Basically we have a third party application that is entering data into a table. They have their own SQL login that is locked down just to the database created for that application to dump the data to.
Then, off of one of the tables that data gets inserted, we have a trigger, that selectively parses the data and inserts it into another table in another database using a different user. We'll call this UserB.
The problem is that when the trigger runs, we get Server Principal 'UserB' is not able to access the database 'dbName' under the current secuirty context.
UserB has FULL access to dbName, I even tried it as DB owner. Google has not been much help. I tried taking Collation off of the database details, that didn't work.
The trigger is run:
WITH EXECUTE AS 'UserB'
Any ideas? Sql Server 2008 R2 by the way.