This statement displays the local and remote server information recorded in master..sysservers:
select srvid, srvname from sysservers
srvid srvname ----- ---------- 0 SALES 1 CORPORATE 2 MARKETING 3 PUBLICATIONS 4 ENGINEERING
The SALES server is local. The other servers are remote.
This statement displays information about the remote servers and users stored in master..sysremotelogins:
select remoteserverid, remoteusername, suid from sysremotelogins
remoteserverid remoteusername suid -------------- -------------- ------ 1 joe 1 1 nancy 2 1 NULL 3 3 NULL 4 4 NULL -1
By matching the value of remoteserverid in this result and the value of srvid in the previous result, you can find the name of the server for which the remoteusername is valid. For example, in the first result, srvid 1 indicates the CORPORATE server; in the second result remoteserverid 1 indicates that same server. Therefore, the remote user login names “joe” and “nancy” are valid on the CORPORATE server.
The following statement shows the entries in master..syslogins:
select suid, name from syslogins
suid name ------ ------------ 1 sa 2 vp 3 admin 4 writer
The results of all three queries together show:
The remote user name “joe” (suid 1) on the remote CORPORATE server (srvid and remoteserverid 1) is mapped to the “sa” login (suid 1).
The remote user name “nancy” (suid 2) on the remote CORPORATE server (srvid and remoteserverid 1) is mapped to the “vp” login (suid 2).
The other logins from the CORPORATE server (remoteusername “NULL”) are mapped to the “admin” login (suid 3).
All logins from the PUBLICATIONS server (srvid and remoteserverid 3) are mapped to the “writer” login (suid 4).
All logins from the ENGINEERING server (srvid and remoteserverid 4) are looked up in master..syslogins by their remote user names (suid -1).
There is no remoteserverid entry for the MARKETING server in sysremotelogins. Therefore, users who log in to the MARKETING server cannot run remote procedure calls from that server.
The remote user mapping procedures and the ability to set permissions for individual stored procedures give you control over which remote users can access local procedures. For example, you can allow the “vp” login from the CORPORATE server to execute certain local procedures and all other logins from CORPORATE to execute the procedures for which the “admin” login has permission.
In
many cases, the passwords for users on the remote server must match passwords
on the local server.
