Problem: Unable to run a query through a linked server SQL Server 2008 R2. This problem occurs with a non-sysadmin account.

Error Message:

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)

Cause: When creating a linked server with the parameter @provstr and you use a local SQL Server non-admin or non-Windows account, you have to add the parameter “User Name”   into the @provstr

Note: Also do not use ‘SQL Server’ (note space) as product name, as you cannot specify a provider or any properties for product ‘SQL Server’

Resolution : Add “User ID=remoteLogin” into the provider string on your linked server

EXEC master.dbo.sp_addlinkedserver @server = N’LinkServerName’, @provider=N’SQLNCLI’,@srvproduct = ‘SQLSERVER’, @provstr=N’SERVER=serverNameInstanceName;User ID=remoteLogin’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’LinkServerName’, @locallogin = ‘localLogin’ , @useself = N’False’, @rmtuser = N’remoteLogin’, @rmtpassword = N’*****’

Check:

SELECT * FROM OPENQUERY ([LinkServerName], ‘SELECT * FROM sysobjects’)