--
--The Entire Operation Takes Place on the Local Instance
--
USE [master]
GO
--Create the linked server object
EXEC master.dbo.sp_addlinkedserver
@server = N'[LinkedServerName]',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'[DestSQLInstanceName]'
--Set up the user mapping between local and remote instances
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'[LinkedServerName]',
@useself=N'False',@locallogin=N'[LocalLogin_SQL_or_Windows]',
@rmtuser=N'[Remote_SQL_Login_Name]',
@rmtpassword='[Remote_SQL_Login_Password]'
GO
--Example of querying a remote table
select * from [LinkedServerName].[Database_Name].[Schema_Name].[Table_Name]
For more info, check out the following links: