Logical Server Names
You might want to set up multiple links to the same target server, an obvious reason for this being to lock down security. You might want one set of users to access one database and another to access a different database, but not let the two groups have access to both databases.
The code below will set up two logical linked servers to the same machine--one will be designed to access the PUBS database (called fuji_PBS_DB) while the other (fuji_NORTHWIND_DB) gives access to the Northwind database.
To test the code, you will need to set up two logins on your target server, called fuji_NORTHWIND and fuji_PUBS, and grant access to each in the relevant databases.
sp_addlinkedserver fuji_PBS_DB, droplogins @server='fuji_PUBS_DB', @srvproduct='', @provider='SQLOLEDB', @datasrc='FUJI' go sp_addlinkedsrvlogin @rmtsrvname = fuji_PUBS_DB , @useself = false , @locallogin = NULL , @rmtuser = fuji_PUBS , @rmtpassword = fuji_PUBS go sp_addlinkedserver fuji_PBS_DB, droplogins @server='fuji_NORTHWIND_DB', @srvproduct='', @provider='SQLOLEDB', @datasrc='FUJI' go sp_addlinkedsrvlogin @rmtsrvname = fuji_NORTHWIND_DB , @useself = false , @locallogin = NULL , @rmtuser = fuji_NORTHWIND , @rmtpassword = fuji_NORTHWIND go
Again, we can run our simple test to demonstrate the difference between the two servers:
select * from openquery (fuji_PUBS_DB, 'select @@serverName, user_name(), db_name(), @@spid') select * from openquery (fuji_NORTHWIND_DB, 'select @@serverName, user_name(), db_name(), @@spid')
[Each of the above selects originally appeared on a single line. We've wrapped them to multiple lines here for Web formatting purposes. -Ed.]
A Point About Security
What we have done here is to set up two linked servers and map all the logins from the local server onto one specific login on the linked server. This means that anyone who knows about the existence of the linked server setup and has access to the local server will also have access to the relevant data on the linked server as well.
Given that I said we wanted to separate the two groups of users and not give them access to the other group's data, this is not exactly an ideal solution.
In this instance it would be better to map individual logins on the local server to their required login on the linked server.
Another Point About Security
As this technique can 'open up' your servers if you do not use it carefully, it's probably best not to shout about it. Do not give the functionality directly to end users--encapsulate it in stored procedures, functions or client-side code, and keep strict controls on how users and developers use it. Users only need their data--they don't need to know how it got there.
Using Your Linked Server
Having set all this up, what can we to with it? Let's look at some examples:
We have already seen the basic syntax for accessing the remote data:
Select * from openquery (server, 'your query here')
This causes SQL Server to open a connection on the target server, log in, execute the query you specified, return the data to the server you are working on, and finally shut down the link.
This query does exactly the same thing, but the syntax is much neater and more flexible to use:
select * from fuji_PUBS_DB.pubs.dbo.authors
With this simplified syntax we can easily join tables on different servers. On my remote server I changed the name of a couple of people in the authors table of the PUBS database, and produced this simple query to return the data that was different across the two servers:
select r.au_fname as remote_fname, r.au_lname as remote_lname, l.* from authors l join fuji_PUBS_DB.pubs.dbo.authors r on l.au_id = r.au_id where l.au_fname <> r.au_fname OR l.au_lname <> r.au_lname