Creating a linked server that has a different name to the "real" server

It's sometimes useful to be able to have a linked server where the name is misleading. One example of this could be where you don't have a perfectly isolated test or staging infrastructure so you can't re-use a server name, but need to reference a linked server in a stored procedure or other database object. You could jump through hoops using a lookup table and dynamic SQL, or, you could create a linked server on your test server where the name is that of the production server but the linked server is pointing towards another part of your test infrastructure, thus keeping it isolated.

The easiest way to do this is to perform the linked server creation via SQL, so here's a script that I generated from the SSMS "New Linked Server" wizard and then tweaked:

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver 
		@server = N'NAME_OF_LINKED_SERVER', 
		@srvproduct=N'sql_server', 
		@provider=N'SQLNCLI11',
		@datasrc=N'NAME_OF_SQL_INSTANCE_TO_TARGET'
GO
EXEC master.dbo.sp_addlinkedsrvlogin 
		@rmtsrvname=N'NAME_OF_LINKED_SERVER',
		@useself=N'False',
		@locallogin=NULL,
		@rmtuser=N'LOGIN_ON_TARGET_SERVER',
		@rmtpassword='PASSWORD_FOR_LOGIN_ON_TARGET_SERVER'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'NAME_OF_LINKED_SERVER', @optname=N'remote proc transaction promotion', @optvalue=N'true'

The key thing is to perform a find and replace on the following values:

• NAME_OF_LINKED_SERVER - This is the name that you want to be able to refer to the linked server by in SQL (i.e. in my example, the name of the "real" server)
• NAME_OF_SQL_INSTANCE_TO_TARGET - This is the name of the actual server your linked server will be targeting
• LOGIN_ON_TARGET_SERVER - This is the SQL user that you want to use to connect to the target server. It should be a login that is created on the target server
• PASSWORD_FOR_LOGIN_ON_TARGET_SERVER - This is the password for the SQL user that exists on the target server that is being used to connect to it

This has been tested, and worked for me, on SQL Server SQL Server 2012 (SP3).