Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 5, 2003

Linked Severs on MS SQL Part 3 - Page 2

By Don Schlichting


By using the Local Login dialog boxes, we can map local SQL users to remote users. The Impersonate checkbox maps the local user, (sa in this example), to the remote login "sa". The local login must match exactly with the remote login for this to work.

Remote User

One to one user mappings can be done by selecting a Local Login, leaving Impersonate unchecked and specifying a Remote User and Password.

From Query Analyzer, the security syntax is:

EXEC sp_addlinkedsrvlogin 'TEST1', 'false', 'donLocal, 'donRemote', 'remotePassword'


To remove a linked server login, use the sp_droplinkedsrvlogin function. This function has only two arguments, the name of the server, and the login to be removed. The syntax for removing the login "donLocal" from above is:

EXEC sp_droplinkedsrvlogin 'TEST1', 'donLocal'

When the linked server is first created, a default login mapping is created. To remove this mapping, run the same statement using NULL as the user name.

EXEC sp_droplinkedsrvlogin 'TEST1', NULL


Distributed queries will be slower than single server selects. The fact that we are communicating over the wire to remote machines implies a performance hit. However, there are methods to minimize performance problems.

  • Use the OpenQuery function rather than a direct reference. (See part 2 for OpenQuery examples.)

  • Execute stored procedures on the remote if possible. To run a remote stored procedure, use the syntax:

    SELECT * FROM OpenQuery(TEST1, 'northwind.dbo.[Ten Most Expensive Products]')
  • Avoid data conversion operations if possible.

  • Set Collation to true when both servers have the same sort order and character set. This will prevent the remote server from sending the entire table over the wire to the local server when a WHERE clause is used. The collation is an option of the sp_ServerOption function. For our example, the syntax would be:

    EXEC sp_serveroption 'TEST1', 'collation compatible', 'true'


As with all things programmed, the necessary security will add additional complexity. However, SQL has given us some very usable tools to deal with it.

Hey, is your BOL a little sparse on SQL to Oracle linked servers. Join us next month for Linked Servers Part 4: Oracle.

» See All Articles by Columnist Don Schlichting

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM