Accessing objects (tables..) across servers (SQL Server) is very simple.

Step 1: Create a link between two servers

Goto Query Window

[sourcecode language='sql']
EXEC sp_addlinkedserver
   ‘PRODSVR’,
   N’SQL Server’
GO[/sourcecode]
‘PRODSVR’  is the name of the Server.Execute it.

Step 2: Setup Permission between Servers.In Management StudioGotoServer Object >> Linked Servers >>

PRODSVR (See Image)

Right Click on PRODSVR goto Properties and make changes as given below.

Step 3: To access the tables in other server

Use the following  hirearchy

PRODSVR.dbname.dbo.tablename

To access Person.Address table from AdventureWorks database

[sourcecode language='sql']

Select * From PRODSVR.AdventureWorks.Person.Address

[/sourcecode]
Advantages :

1. Update Production server with data from development server. (SSIS is alternate way)

2. Allow users to access production database without multiple logins. (windows authentication or SQL authentication)