0

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.

4
  • Which version of SQL Server? Do you have permissions to query all databases with the account you are logged in as? Commented Sep 15, 2014 at 21:30
  • 2008 R2. Yes, I'm the owner.
    – n8.
    Commented Sep 15, 2014 at 21:44
  • 1
    on the server that you can see all the databases what permissions do you have? Not at each db level but at the sql server level?
    – Jeff A
    Commented Sep 15, 2014 at 21:48
  • My IT Director has sysadmin and tried what I am trying to do, so it is not a permissions issue. He just pointed out that the server is SQL Server Express edition, could this be a limitation?
    – n8.
    Commented Sep 16, 2014 at 22:01

1 Answer 1

1

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/

3
  • Interesting, but that's not it. On server 1 the databases can all see and query each other (except where security permissions forbid it) and this option is not selected. We tried what you suggested on server 2 and it did not resolve the issue. It's also worth noting that the "Cross-database Ownership Chaining Enabled" option is "false" and grayed out, and so cannot be changed to "True".
    – n8.
    Commented Sep 16, 2014 at 21:59
  • So you are trying to query across databases on different servers? How can you try the setting if it is grayed out? Commented Sep 17, 2014 at 8:22
  • The setting at the server level is a checkbox and that control is active. When you browse individual databases there is a property for "Cross-database Ownership Chaining Enabled" on those as well, drop-down true/false selectors that are disabled.
    – n8.
    Commented Sep 17, 2014 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.