Marko Zivkovic
C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\9.png

How to create, configure and drop a SQL Server linked server using Transact-SQL

July 5, 2017 by

Linked servers allow getting data from a different SQL Server instance using single T-SQL statement.

This article will explain how to create, configure and drop a SQL Server linked server using system stored procedures.

Also, there is a way to create and configure a linked server to another SQL Server instance using SQL Server Management Studio (SSMS). To see how to configure a linked server by using the SSMS GUI, see the article How to create and configure a linked server in SQL Server Management Studio.

Creating a SQL Server linked server

To create a linked server, use the sp_addlinkedserver procedure.

Executing the following code:

This will create a linked server with the name TestServer under the Linked Servers folder:

In order to get databases from the remote SQL server the exact name of the SQL Server need be entered.

For default instance of the SQL Server type the name of the computer that hosts the instance of SQL Server (e.g. WSERVER2012):

For the SQL Server name instance, type the name of the computer and the name of the instance separated by slash (e.g. WSERVER2012\SQLEXPRESS):

The results will be something like this:

When creating a linked server like this by default, it will pass the current security context of the local login to the remote login.

Window or the local login credentials, will be used if Windows or SQL Server authentication is selected, respectively. In order to successfully connect to a remote SQL Server, the exact user with the same username and password must exist on the remote server:

Besides defining a parameter for the name of the linked server and the product name, while using sp_addlinkedserver, other parameters such are: provider name, data source, location, provider string, catalog can be set:

@server is a name of the linked server:

@srvproduct is the product name of the OLE DB data source you’re adding as a linked server (e. g ‘SQL Server’, ‘Microsoft.Jet.OLEDB.4.0’,’ Oracle’ etc.). The @srvproduct parameter is nvarchar(128) data type, by default this parameter is NULL. When using SQL Server as the product name, the provider name, data source, location, provider string, and catalog parameters do not have to be specified:

@provider is the OLE DB provider name and must be unique for the specified OLE DB provider installed on the current computer:

The @provider parameter is nvarchar(128) data type, by default this parameter is NULL. If the provider name parameter is omitted, SQLNCLI (SQL Server Native Client) is used:

@datasrc is the name of the data source as interpreted by the OLE DB provider. If you are connecting to an instance of SQL Server, provide the instance name:

When creating a linked server like this, a name of the server in the @server parameter can be anything, does not need to be a name of a remote server:

If you are using another data source (e.g. Excel file), provide the full path to the Microsoft Excel file in the @datasrc parameter:

@location is the location of the database (source), if required by the OLE DB provider. The @location parameter is nvarchar(4000) data type, by default this parameter is NULL.

@provstr is the OLEDB string that identifies the source. The @provstr parameter is nvarchar(4000) data type, by default this parameter is NULL. If the linked server created using SQLNCLI then in the @provstr parameter can be specified the instance of SQL Server to which the user will be connected:

@catalog is the name of the catalog or the name of the database for the SQL Server, by default this parameter is NULL. When the linked server is defined against an instance of SQL Server, the catalog refers to the default database to which the linked server is mapped.

More about sp_addlinkedserver parameters can be found on this link.

Create or update a mapping between a login on the local SQL Server instance and account on a remote SQL Server

After creating a linked server using sp_addlinkedserver procedure:

Use the sp_addlinkedsrvlogin to create or modified a mapping a local logins to the remote logins using the following syntax:

@rmtsrvname is the name of the remote server (linked server) which login mapping applies to:

@useself is used to determine how connection with remote server will be established. There are three states ‘True’, ‘False’, ’Null’.

If the ‘True’ state is used, then the current security context of the local login will be passed to the remote login.

Window or the local login credentials, will be used if Windows or SQL Server authentication is selected, respectively

When executing the above SQL code, the following results will appear in the Catalogs folder for SQL Server Authentication and local user ‘Jack’:

Note: To successfully connect to a remote server the user with the exact same username and password must exist on the remote server.

In case when connecting to local SQL Server using SQL Server Authentication with local user login credentials that doesn’t exist on the remote server (e. g. user ‘Ben’), the results under the Catalog folder may look like this:

The same security context is used when creating a linked server with the sp_addlinkedserver procedure:

The @useself parameter is varchar(8) data type, by default this parameter is set to ‘True’.

If the ‘False’ value is set to @useself parameter, then the local server login use the arguments specified in the @rmtuser and @rmtpassword parameters to log in to the linked server:

Now, when local user ‘Ben’ login to local SQL Server, under the Catalogs folder, all databases that are available on a remote server for the ‘Jack’ remote login will be listed:

For a linked server that doesn’t require username and password (e.g. Microsoft Access), these arguments can be set to Null.

@locallogin is a local login, by default this parameter is set to Null. You can designate either an individual login or all local logins. To specify that all local logins be affected pass a Null to this parameter. If not Null, then in the @locallogin parameter can be a SQL Server login or a Windows login.

@rmtuser is the username of the remote login used to connect to a remote server if @useself is set to ‘False’.

@rmtpassword is the password of the remote user used to connect to a remote server if @useself is set to ‘False’.

Set a SQL Server linked server options

Additionally, you can configure a linked server by using sp_serveroption procedure. Here you can set the vireos of options related to a linked server like: collation compatible, collation name, connect timeout, data access, lazy schema validation, rpc, rpc out, use remote collation etc., by executing the following code:

@server is the name of the linked server for which to set the option.

@optname the name of the option to be configured.

@optvalue the value of the option. Valid values are True or On to enable and False or Off to disable the option, a non-negative integer for the connection timeout and query time out options, or collation name for the collation name option.

The following example, enables remote procedure calls to the ‘WSERVER2012\SQLEXPRESS’ linked server:

To allow/deny a linked server for distributed query access, use the @optname ‘data access’. The following example, disable distributed query to ‘WSERVER2012\SQLEXPRESS’ linked server

Now when execute a linked server query:

The following error message will appear:

Msg 7411, Level 16, State 1, Line 1
Server ‘WSERVER2012\SQLEXPRESS’ is not configured for DATA ACCESS.

To set the query timeout use the @optname ‘query timeout’. Here can be set how long, in seconds, a remote process can take before time is out. From the example below query timeout is set to 120 seconds:

More about options under the sp_serveroption procedure can be found on this link.

Getting information(settings) about a SQL Server linked server

To see information about linked servers and the referenced data sources, SQL Server Management Studio or the system stored procedures can be used.

Below is the list of the most commonly used system stored procedures.

To see all about login mappings defined against a specific linked server, run the sp_helplinkedsrvlogin procedure.

@rmtsrvname the name of the linked server for which want to see login setting displayed, by default this parameter is Null

@locallogin the local login that has a mapping to the linked server, by default this parameter is set to Null

In the example below are displayed all login mappings for all linked servers defined on the local machine:

The result will be something like this:

Linked Server Local Login Is Self Mapping Remote Login
WSERVER2012\CTP21 Null 1 Null
WSERVER2012\SQLEXPRESS Null 1 Null
WSERVER2012\SQLEXPRESS WSERVER2012\Zivko 1 Null
WSERVER2012\SQLEXPRESS Ben 0 Jack

Linked Server is the name of the linked server.

Local Login displays which local users are affected by this mapping. If Null is set, this mapping applies to all users who don’t have specific mappings.

Is Self Mapping, if it displays 1, the local login and password are used when connecting to a linked server. If 0 displayed, then the value from the Remote Login column are used for connecting to a linked server. Note, remote password is not displayed for security reason.

To display all login mappings for a specific linked server, execute the following:


Linked Server Local Login Is Self Mapping Remote Login
WSERVER2012\SQLEXPRESS Null 1 Null
WSERVER2012\SQLEXPRESS WSERVER2012\Zivko 1 Null
WSERVER2012\SQLEXPRESS Ben 0 Jack

To displays all login mappings for a local login, execute the following:


Linked Server Local Login Is Self Mapping Remote Login
WSERVER2012\SQLEXPRESS Ben 0 Jack

sp_linkedservers – this procedure returns a list of linked servers defined on the local server


sp_catalogs – this procedure displays a list of catalogs for the specified linked server

Basically, this displays a list of available databases for the chosen linked server:

sp_tables_ex – shows the table information about the tables for the specified linked server:


sp_columns_ex – shows column information for all columns, or a specified column in a remote table


sp_table_privileges_ex – displays table permissions for a linked server table.


sp_column_privileges_ex – displays a list privileges for columns on a specific table for a linked server


sp_testlinkedserver this procedure tests the connection to a linked server:

If the test fails, it returns an error message with the reason of the failure.

Below is an example in which creates a linked server named ‘WSERVER2012’ and then tests the connection:

Test a linked server connection:

Reason for the failure:

OLE DB provider “SQLNCLI11” for linked server “WSERVER2012” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI11” for linked server “WSERVER2012” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
Msg 2, Level 16, State 1, Line 6
Named Pipes Provider: Could not open a connection to SQL Server [2].

To see the settings for a linked server from SQL Server Management Studio, right click on linked server under the Linked Server folder and chose Properties command:

This will open the Linked Server Properties dialog:

On the top right side of the dialog, there are three tabs (General, Security, Server Options) on which can be seen all settings for the chosen a linked server.

To show all setting s for a chosen linked server in a query editor, right click on the name of a linked server under the Linked Servers folder, from the context menu chose Script Linked Server as -> Create to -> New Query Editor Window command:

This will create a .sql script with all settings that contains the chosen linked server:

Delete a mapping

To delete an existing mapping between a local login and login on a linked server use the sp_droplinkedsrvlogin system stored procedure.

@rmtsrvname is the name of a linked server that the login mapping applies to. A linked server must exist under the Linked Server folder, otherwise the following error message my appears when execute the sp_droplinkedsrvlogin procedure:

Msg 15015, Level 16, State 1, Procedure sp_droplinkedsrvlogin, Line 32 [Batch Start Line 0]
The server ‘WSERVER2012’ does not exist. Use sp_helpserver to show available servers.

@locallogin is the local login that has mapping to a linked server. A mapping for local login to a remote server must exist, otherwise the following error message may appear:

Msg 15007, Level 16, State 1, Procedure sp_droplinkedsrvlogin, Line 51 [Batch Start Line 0]
‘Jana’ is not a valid login or you do not have permission.

If the @locallogin property is set to Null, the default mapping which is created when a linked server is created by sp_addlinkedserver procedure will be removed.

The following example will remove the login mapping for the ‘Ben’ local user to the ‘WSERVER2012\SQLEXPRESS’ linked server:

This code will remove the default login mapping for all users to the ‘WSERVER2012\SQLEXPRESS’ linked server:

Delete a SQL Server linked server

To remove a linked server, form the Linked Server folder use the sp_dropserver procedure:

@server is the name of the server that will be removed.

@droplogins if the ‘droplogins’ value is set, then all logins for the specified remote server will be removed, by default the @droplogins property is set to Null.

Now, when executing this code:

The following error message will appear:

Msg 15190, Level 16, State 1, Procedure sys.sp_dropserver, Line 56 [Batch Start Line 3]
There are still remote logins or linked logins for the server ‘WSERVER2012\SQLEXPRESS’.

To resolve this problem, there are two solutions:

  1. Remove all logins related to a linked server by using the sp_droplinkedsrvlogin procedure and then execute the above mentioned code
  2. Instead of a Null value for the @droplogins property in the sp_dropserver procedure, just put the ‘droplogins’ value and all logins related to a chosen linked server will be removed together with a linked server:

Other articles in this series:

See more

To boost your SQL Server development productivity, check out Free SQL Server Management Studio add-ins.

Useful links


Marko Zivkovic

Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic
Marko Zivkovic
SQL Server Management Studio

About Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic

859 Views