After you have added a linked server, you will need to log on to the server and resource where the data resides. By default, SQL Server will attempt to do this with your current login ID and password. If you do not have access to the requested resources on the linked server, you will need to add a remote login.
Adding a remote login
You can add a remote login with the sp_addlinkedsrvlogin system stored procedure, which follows the syntax shown in the following SlideShow:
To remove a linked server login, use the sp_droplinkedsrvlogin system stored procedure. Include the remote server and local login name, as shown below:
sp_droplinkedsrvlogin 'Boston', 'amann'
Configuring the logins
The whole point of linked servers is to enable local users to run queries that access data from other data sources. If the external data source is SQL Server, then it will require some type of user authentication, which is accomplished via mapping logins, or for those local users whose logins are not mapped, via setting the default behavior. The login map will either pass the user along without translating the login name if the Impersonate option is checked, or translate any user’s login to a remote login and password if the Impersonate option
is not checked. Of course, on the external server, the login must be a valid login and must have been granted security rights in order for the link to be effective.
In the next lesson, you will learn how to use a fully qualified path to access remote data.