Linked Severs on MS SQL Part 3 - Page 2
December 5, 2003
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.
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.