SQL Server copying table information between databases

Question

I have a script that I am using to copy data from a table in one database to a table in another database on the same SQL Server instance. The script works great when I am connected to the SQL Server instance as myself as I have dbo access to both databases. The problem is that this won't be the case on the client's SQL Server. They have seperate logins for each database (SQL Authentication Logins). Does anyone know if there is a way to run a script under these circumstances. The script would be doing something like.

use sourceDB
Insert targetDB.dbo.tblTest (id, test_name)
Select id, test_name from dbo.tblTest

Thanks

Answer 1

You can set up a linked server:

http://www.databasejournal.com/features/mssql/article.php/3085211/Linked-Servers-on-MS-SQL-Part-1.htm

http://msdn.microsoft.com/en-us/library/ms190479.aspx

You can then reference the linked server from within your script.

Answer 2

You will need to give one of the users access to the other database. It can be read access if it is the user with write access to the database where the data is going. Any other solution would require you to go outside of the database and back in again which is not an elegant solution and would leave yourself open to more data issues.