This is a bit of a newbie question I think, but all the answers I've found online are for other questions, so I ask here:
We have a server that we set up on a virtual machine to handle the passing of data from a VPN connection to the main database that end users will access. This "Processing" server has a few databases for handling deltas and hosting sprocs. I have recently tried to create a view in one of those databases and found that it cannot query data in any other database, which is not the normal experience that I have had. On the other server all the databases can see eachother. I've looked at all of the databases and they only have visibility limited to themselves.
I suppose there is a setting at the server level that isolates the databases, but I don't have the expertise to know what that is. Can somebody help me? I'm at an impasse until this is solved.
Thanks,
n8
–edit–
After extended testing I have found that the databases can write to each other and to the other server using 4-part naming, I just cannot execute queries or views that span the same databases that the sprocs are reading from and writing to, even using 4-part naming. This is quite odd.
Best Answer
Please enable this setting:
http://msdn.microsoft.com/en-gb/library/ms188694.aspx http://msdn.microsoft.com/en-us/library/bb669059(v=vs.110).aspx
This allows the security permission between databases to propagate. Additional details described well in this blog: http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/