[AccessD] [dba-SQLServer] Using view or sp from other Db on same Instance of Server

jwcolby jwcolby at colbyconsulting.com
Mon Feb 7 07:15:23 CST 2011


It feels like a security issue.

At my office I am the owner of all the databases.  I have many (hundreds) of databases and I have 
always used the db.dbo.table.view addressing to create views, dynamic SQL etc. most of which 
references other databases.  I have a handful of (a dozen) databases which are what I call "data" 
databases, which hold name / address lists.  In the past I used SPs heavily which operated on data 
from each other.

I also use C# to execute code updating or appending records from one database to another.  the key 
to me is the dbname.dbo. syntax, as long as I have that the queries work fine.  Again though, all of 
the databases are owned by the same owner so rights are not an issue.

John W. Colby
www.ColbyConsulting.com

On 2/5/2011 3:14 AM, Borge Hansen wrote:
> Hi all,
> I have created both a view and a SP based on tables in another Db
> (call it Db1) on the same instance of the SQL Server (2008). I created
> the view and SP in say Db2. A simple Select ...
> When I want to use either the View or the SP in a join with other
> tables in Db2 I am stuck. The SP containing the join to either view or
> SP based on Db1 tables and saved in Db2 will save Ok but not execute.
> Both the view and the SP that I am trying to join up will execute ok
> when running on their own.
> Funny thing is that in the Access FE I can create a pass through query
> based on the view and join the pass through query to an odbc linked
> table from Db2.
> Any one who can shed light on this?
> borge



More information about the AccessD mailing list