Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer

Why not just create a stored procedure on the AX database which simply calls the AX stored procedure. You can put whatever EXECUTE AS into your stored procedure before the AX procedure is called.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.