We are looking at setting up a linked server from one SQL Server to another. We will create the linked server on the primary Database. And it will link to a read-only secondary server - i.e. the server it's linking to is in high availability, replicating from the primary.
The idea put forth was to make a linked server for each Database rather than creating just one. Does anyone find any advantage to that? The rational was to ensure that we can't access the wrong Database i.e. if it's called on the Primary Server as Customer A, it can't retrieve information from Customer B's Database, in the case that there is an error in the Stored Proc. The thing is we already have security in place on each server to prevent this. So I thought that if "Be made using the logins current security context" is chosen, we would get a permission error instead, in case the Stored Procedure is trying to access the wrong Database.