We are setting up our new CRM environment. It involves several databases. We have an all database that contains union views of the other CRM databases. The goal is to give everyone read access to the views & not to the underlying base tables. I am having some trouble getting this to work. Here is what it looks like:
SQL 2005 environment
DB1 - CRM DB #1
DB2 - CRM DB #2
DBAll - union views DB
Test user - has datareader access to DBAll
I have database chaining enabled for all 3 database. I have DBAll set to trustworthy. All objects are owned by dbo.
When I do a select from one of the views, I get a message saying:
"The server principal "testuser" is not able to access the database "DB#1" under the current security context.
I have read through BOL & did some searches online but can't figure out where my issue is.