|Lesson 4||Adding a remote login|
|Objective||Add a remote login and log on to the system. |
Adding Remote Login
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:
Example of adding a remote login
To allow remote access for the following user:
Use this Transact-SQL code:
sp_addlinkedsrvlogin 'Boston', 'false', 'amann',
Removing a remote login
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.