To use the Linked Server, have you set up the user account on the AX server as well as the other? You need it to be in place on both in order to use account impersonation.
Personally, I would go with option (b), if it's not too complicated. Depending on what you need, you could consider providing an SSRS report to expose the data they need and no more.
Based on comments on the question: The desire is to have RemoteSP on [REMOTE] execute within the security context of GenericUser, but only executable by [MYDOMAIN\OneUser] even though everyone accesses [REMOTE] as GenericUser via the same Linked Server with a static security setup.
That seems like an inconsistent security setup. You have a single login on [REMOTE] that you want to have two different sets of permissions for. At the very least you will have to DENY EXECUTE ON [RemoteSP] TO [GenericUser]; in order to prevent everyone outside of [MYDOMAIN\OneUser] from executing the proc over the Linked Server. But you want it to execute in the context of GenericUser so you need to add WITH EXECUTE AS 'GenericUser' to that proc. Both of these steps get you the security context with the restriction on who can run it, but so far there is no way for [MYDOMAIN\OneUser] to execute it either. And yes, it seems counter-intuitive to DENY execute to the User that is then used as the security context, but a) that be the requirements, and b) it does work as I tested it since it seemed like there was a possibility that SQL Server might give the "Did you even look at what you were trying to execute" error ;-).
Allowing only [MYDOMAIN\OneUser] to execute means creating another Login on [REMOTE] for this purpose, call it UserForRemoteSP. Be sure to GRANT EXECUTE ON [RemoteSP] TO [UserForRemoteSP];.
Another desire is that [MYDOMAIN\OneUser] still access [REMOTE] via the current Linked Server (and hence as GenericUser) for everything else besides [RemoteSP]. That can be accomplished by creating another Linked Server, call it [REMOTE_SPECIAL], that has a mapped "local login to remote login" list with only [MYDOMAIN\OneUser] defined in it, mapping to UserForRemoteSP. For the "For a login not defined in the list above, connections will:" option, select "Not be made".
Last step: in [LocalSP], in the IF SUSER_NAME block, just do:
EXEC [REMOTE_SPECIAL].[B].[dbo].[RemoteSP] @MyVar = @Id;
Nobody else will be able to use the [REMOTE_SPECIAL] Linked Server, and the only thing that [MYDOMAIN\OneUser] can use that Linked Server for is to execute RemoteSP, which will run as GenericUser due to the EXECUTE AS clause.
Best Answer
I don't think this is empirically deterministic. My understanding is that an entire table could be brought over and processed on the calling server instead of being processed remotely on the linked server. If both servers are SQL Server 2008+ you should see scenarios like this:
And in these cases you will see that the join, filters etc. are processed on the remote server. But this should not be construed as a blanket statement! You may see slightly different remoting decisions in the plan for a slightly different scenario:
These are for SQL Server 2008+ -> SQL Server 2008+ linked servers. Other linked servers (lower versions of SQL Server, other platforms altogether) may behave differently, and it may be version-dependent, it could even be specific driver-version dependent, or query-dependent (e.g. is a function used). And it of course could depend on the permissions of the linked server account - if you can't see remote statistics / cardinality for the distributed portion of the query, the local optimizer is going to be pretty blind and may not make the decision you want.
If you want to guarantee that processing occurs on the remote server before it starts streaming data across, and you have actually tried to get it work with four-part names and the plan does not try to process remotely, you can use
OPENQUERYinstead of explicitly referencing four-part names. Never tried that in a view, though. :-)If you come across a case where you expect processing to occur remotely and it's not, I'm more than happy to look at the actual plan and the stats profile to help understand why (or at the very least to pass it on to some of my connections who can help).
From my own personal experience, I've found much, much, much more reliable and predictable performance by doing whatever I can to make as many queries as possible entirely local. Linked servers are useful but they certainly don't lend themselves to real-time performance. While you may think it violates DRY in some sense, I would rather pay that cost once during a mirroring session or replication and have redundant data, than potentially pay for a sub-optimal remoting decision for every single cross-server query. And if the data can be slightly delayed there are even more options for moving data across to make it available locally (log shipping, SSIS, custom).