Recently, when I was working with security and changed some level of access and permission of some logins\users, I received an error while accessing data through linked servers with some logins which was working earlier. An error is reported as below.
Msg 7416, Level 16, State 2, Line 1After finding solution following, it worked. Here is a some change of linked server and below is a script to used for same. Just adding a logins to linked server which has an issue to access it.
Access to the remote server is denied because no login-mapping exists.
Use master GO EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @provider=N'SQLNCLI', @srvproduct = 'MS SQL Server', @provstr=N'SERVER=ServerName\InstanceName;User ID=myUser' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkedServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****' GOHere is the just script and change your user name in place of ‘myUser’ and appropriate server\instance name. Please share your comments if you received such errors and workaround for same.