hscast
asked on
Trigger causing error "Server Principal is not able to access the database under the current secuirty context"
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'
AFTER INSERT
Any ideas? Sql Server 2008 R2 by the way.
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'
AFTER INSERT
Any ideas? Sql Server 2008 R2 by the way.
tim_cs
Did you give the owner of Database1 AUTHENTICATE permission on Database2 or set Trustworthy to on for Database1?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.