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.
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.