I agree that it's best to map local logins to an account on the remote server, rather than have all logins access the linked server using a default security context. Otherwise, even an account with nothing but PUBLIC access will have equal access to the linked server.

Mapping local logins to remote accounts can be done even when the remote server doesn't have (or doesn't support) windows authentication. Here is how one my linked servers are configured where the remote server is Oracle. A windows domain account, MYCORP\ReportingUser, can access the linked server under the context of the remote Oracle account, RPT_USER.

Local server login to remote server login mappings:

Local Login: MYCORP\ReportingUser

Impersonate: No

Remote User: RPT_USER (account on remote Oracle server)

Remote Password: ########

For a login not defined in the above list, connections will:

- Not be made