When setting up security for linked servers, what does the Impersonate checkbox do?

Question

Question:

what does this checkbox do?

Answer 1

Impersonate

Pass the username and password from the local login to the linked server. For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. For Windows logins, the login must be a valid login on the linked server.

To use impersonation, the configuration must meet the requirement for delegation.

Create Linked Servers (SQL Server Database Engine)

In other words, this option corresponds to code

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'remote_server', @locallogin = N'domain\account', @useself = N'True'

Answer 2

When the impersonate checkbox is marked and the linked server is used by the listed login, SQL Server will use the local Windows login to connect to the remote instance. This requires delegation to be correctly set up (SQL Server needs to act as a Windows account).

One usually use impersonations when you want to keep the security context amongst Windows.

Either you use Impersonation or a RemoteUser with RemotePassword, not both on the same login.