Access to the remote server is denied because no login-mapping exists.

    

Problem: Unable to run a query through a linked server SQL Server 2005. This problem only happens with a non-sysadmin account.

You got the message below:

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists..

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

Resolution : Add "User ID=Username" into the provider string on your linked server

EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @provstr=N'SERVER=serverName\InstanceName ;User ID=myUser'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****'

Check:

SELECT  TOP 1 * FROM LinkServerName.msdb.dbo.backupset
GO
SELECT * FROM OPENQUERY (LinkServerName, 'SELECT TOP 1 * FROM msdb.dbo.backupset ')

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |