?
Solved

Trigger causing error "Server Principal is not able to access the database under the current secuirty context"

Posted on 2012-08-21
3
Medium Priority
?
422 Views
Last Modified: 2013-07-02
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.
0
Comment
Question by:hscast
  • 2
3 Comments
 
LVL 15

Expert Comment

by:tim_cs
ID: 38316667
Did you give the owner of Database1 AUTHENTICATE permission on Database2  or set Trustworthy to on for Database1?
0
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 750 total points
ID: 38316703
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 750 total points
ID: 38318808
You need to allow Cross-Database Ownership Chaining on both databases, as in:
ALTER DATABASE SourceDatabaseName SET DB_CHAINING ON
ALTER DATABASE TargetDatabaseName SET DB_CHAINING ON
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question