Login Failed for User Cannot open user Default Database. Login Failed(Microsoft SQL Server, Error:18456) on Linked Server

Note:

kindly create linked server with SQL Authentication with sysadmin it should exists on both server as same sysadmin credentials

Step 1:

EXEC sp_addlinkedserver
   @server=N'LinkedServerName',             -- Remote Computer Name linked server name
   @srvproduct=N'',                 -- Not Needed
   @provider=N'SQLNCLI',            -- SQL Server Driver
   @datasrc=N'SQLEXPRESS2008'; --Machine Server Name \Instance Name


EXEC sp_addlinkedserver
   @server=N'LinkedServerName',             -- Remote Computer Name linked server name
   @srvproduct=N'',                 -- Not Needed
   @provider=N'SQLNCLI',            -- SQL Server Driver
   @datasrc=N'WELCOME-PC\SQLEXPRESS2008'; --if instance name is null then machine name enough


Step 2:

EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'true';

 ----to solve logine failed for user before user muste be existed on both source and destination with sysadmin login----

Step 3:

 EXEC sp_addlinkedsrvlogin       @rmtsrvname='LinkedServerName',
                                 @useself=N'False',
                                 @locallogin=NULL,
                                 @rmtuser='LinkedServerLogin',
                                 @rmtpassword='password'

Step 4:

SELECT * FROM linkedservername.databasename.dbo.tablename

Ex:-

Select * from TestLinkedServer2008.DMVTest.dbo.t1

Step 5:

Exec sp_readerrorlog---------------to know error state


Demo:

Login name 'VerifyLogin' have public privilges not have sysadmin it did nt exists on

Destination(SQL2005 LinkedServer Name). When you Right Click of SQL2005 LinkedServerName

and Click Test Linked Serer Connection then you will get Error as see below

When you tested on Source Server you know it have public login 

It is due to login have doesn't have exist on Destionation(SQL2005) Server.

So After Create it Login on Destination  Server with Public or any privilges it worked correctly.