January 30, 2005 at 10:53 pm
SELECT a.i, a.j
FROM pubs.dbo.Table1 a
INNER JOIN
northwind.dbo.Table2 b
ON a.i = b.i
Go
Here is an example of linking between databases, would this still work if pubs was a Tempdb, are there any issues with dbo ??
Like
SELECT a.i, a.j
FROM tempdb.dbo.Table1 a
INNER JOIN
northwind.dbo.Table2 b
ON a.i = b.i
GO
January 31, 2005 at 5:36 pm
It would work if dbo is mapped to the same login in both databases. This is an example of cross-database ownership chaining (see BOL).
By the way, it's not a good practice to create 'permanent' tables in tempdb as the database is recreated each time SQL Server is started. Temporary tables should start with '#' or '##' so they are dropped automatically on disconnect.
Hope this helps,
Greg
Greg
January 31, 2005 at 6:37 pm
Concerning... this code..
SELECT a.i, a.j
FROM tempdb.dbo.#Table1 a
INNER JOIN
northwind.dbo.Table2 b
ON a.i = b.i
GO
What if "tempdb.dbo.#Table1" was going to be created by any user, like....tempdb.Andrew.#Table1 a or tempdb.Bob.#Table1 or tempdb.John.#Table1 a
QUESTION : Is it possible to create a Variable or function in the join like ... tempdb." & @UserName & ".#Table1 a .... to cater for any user to make this join work with any user creating the #Table1 that joins to the consistently named "northwind.dbo.Table2" object????
What is the best practice in this situation ?
January 31, 2005 at 11:56 pm
You can not use variables in table names unless you build the entire query as a variable and then use EXEC(@variable) to execute the query. You could query the sys tables for ownership and use the results of the query to decide how to build your query prior to execution.
Hope this helps
Marvin
Marvin Dillard
Senior Consultant
Claraview Inc
February 1, 2005 at 10:34 am
Marvin is correct about not being able to use a variable for a table name, but I don't think you need to anyway since temporary tables aren't created with the creator's schema name. If user Andrew created ##Table1, it would be useable by any user. It would be referenced by the name ##Table1.
Greg
Greg
February 1, 2005 at 10:38 am
what is the correct technique to do joins between tables when the tables Or objects have been created by different owners.
the owner dbo is from sysadmin role, a user(client) owner ( like Tim, bob, John) could be from db_datareader role ?
February 1, 2005 at 7:41 pm
I think I am sorted out now.
When doing read only query of a #temp table with a join to dbo.table, the different owners of the object does not matter.
This is not so when updating, deleting, etc
February 4, 2005 at 12:32 pm
I searched "Ownership Chains" on BOL...it helped a lot !
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply