We have an AX server that we would like a group of users to be able to retrieve data from but limit their access to just the data needed. Normally this could be done with a stored procedure and give the the group permission to only execute the stored procedure and use the "execute as" to have the procedure run under an account that does have access to the underlying tables. However AX controls the lifetime of stored procedure so we can't just create an arbitrary stored procedure and call it good.
To remedy the situation, we would instead have the stored procedure on another SQL Server and use a Linked Server so that the users could get the data they need but not have direct access to the tables of the AX server.
The problem is in setting this up we keep getting:
Msg 7437, Level 16, State 1, Line 3
Linked servers cannot be used under impersonation without a mapping for the impersonated login.
So the question is: How do we get this scenario to work with Linked Servers or is there a better way to have users get the data they need without direct access to the AX server?
Best Answer
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.