I have a local SQL Server 2008R2. I have configured Linked Server to a remote database.
The Linked Server works great when I login to the local server using a SQL-login account with sysadmin server role. I can query against the remote server, so I know the Linked Server setting is correct. However, I would get the error below if I use an account that does not have the sysadmin server role.
Msg 7416, Level 16, State 2, Line 2
Access to the remote server is denied because no login-mapping exists.
For both local and remote servers, SQL login is used (Windows authentication is not used)
What kind of security I need to configure for a regular SQL-login account to use Linked Server?
As alternative solution you can use the parameter @datasrc instead of @provstr. @dataSrc works without setting the User ID
Sample:
EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @datasrc=N'serverName\InstanceName'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****'
I've added a comment here, too, but it's not visible (don't know why).