Impersonate
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'
Sp_DropLinkedSrvLogin
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
PERFORMANCE
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'
Conclusion
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
» 
Comment and Contribute
